Returing the highest value?? (using SQL??)

[UK-Dave]

Hi, sorry if this is seems very simple, however I will fire away....

I have 2 data sets, the first is a vector layer which represents the
geolgy of the land (with catogories 1-6) and the 2nd is a set of
polygons which represent our site boundries.


What I am attempting to do is return the highest geolgical value which
is contained within a site boundary polygon.

(at the moment I am using the Contains SQL funtion) and this is just
returning the value of the centroid i believe(?) and not the highest
value within the polygon..)

Any assistance would be greatfully recieved

[Lawley, Russell S]

Uk-Dave,

Assuming a geology table called geoltab with a 'geology value' field held as a number called geo_id and a site boundary table called 'site' with a unique id called site_id the following sql should provide the answers:



Select site.site_id, max(geoltab.geo_id) from site, geoltab where site.Obj intersects geoltab.Obj group by site.site_id order by site.site_id into Selection
Browse * From Selection

This will return the 'highest' value from any amount of site-geology intersection, so a tiny overlap of a high value will 'load' your site, even if it is mostly underlain by a low geology value..so some caution may be needed..and don't forget to check rigorously as I have found queries using the MAX operators to behave oddly with some data

Regards

Russell Lawley
Brit. geol. surv

--

You received this message because you are subscribed to the Google Groups "MapInfo-L" group.
To post to this group, send email to mapi...@googlegroups.com.
To unsubscribe from this group, send email to mapinfo-l+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/mapinfo-l?hl=en.



--
This message (and any attachments) is for the recipient only. NERC
is subject to the Freedom of Information Act 2000 and the contents
of this email and any reply you make may be disclosed by NERC unless
it is exempt from release under the Act. Any material supplied to
NERC may be stored in an electronic records management system.

[UK-Dave]

Than you very much, just the job!! U have saved me a big headache!

> For more options, visit this group athttp://groups.google.com/group/mapinfo-l?hl=en.

>
> --
> This message (and any attachments) is for the recipient only. NERC
> is subject to the Freedom of Information Act 2000 and the contents
> of this email and any reply you make may be disclosed by NERC unless
> it is exempt from release under the Act. Any material supplied to

> NERC may be stored in an electronic records management system.- Hide quoted text -
>
> - Show quoted text -

[kompas]

Hi guys,

to explore a topic a bit, on the same example how would you perform
selecting of n highest values in the boundary to be able to define n
on a per query basis?
Bests,
Jakub

[Lawley, Russell S]

Hi kompass, 

I have had to do this on numerous occasions, , the method i use is to create a look-up table of all the sites and their geology intersections (using a similar sql to that given to Uk_dave):

 

Select site.site_id, geoltab.geo_id from site, geoltab where site.Obj intersects geoltab.Obj order by site.site_id, geoltab.geo_id into mylookup

 

I then use a bit of mapbasic code to crawl down thru the lookup table looking at the top n  records per unique site id (say  top 3)

 

So eg  if mylookup looks like this, and i want the top three hits

 

Site1, geol987 **

Site1, geol88  **

Site1, geol75  **

Site1, geol6

Site2, geol987 **

Site2, geol68  **

Site3, geol75  **

Site3, geol6   **

Site3, geol5   **

Site3, geol3  

 

Then only the ** records get returned..Note that site 2 only has two hits to start with so can only return two hits

 

In pseudo code terms you need  something that

 

1. Saves the mylookup as a table in its own right, and then reopens it
2. Requests an  n count value (say 3) via a dialog (or hard code)
3. fetches the first record  of the lookup
4. Reads the siteid in column 1  into an ID_variable  and sets the 'n'count to 1
5. starts a loop  ( i use 'for i = 1 tableinfo(.. nrows)' ..but you could use whatever you prefer)

1. fetches the i^th row,
2. reads the  site_id and geolval from column 1 and column2
3. checks to see if siteid is same as ID_variable

1. if TRUE

1. check to see if ncount ≤ n

1. if true then that record is wanted  so save values or table row to new table  (or whatever you want)
2. if false then this record lies outside the nth range requested

2. increment ncount

2. if FALSE  (it musy be a new site)

3. ncount = 1 (we know this from logic)

a. so save values or table row to new table  (or whatever you want)

4. reset so id_variable = the new siteid value

6. next loop

 

 

 

so, basically a loop, a counter, a comparator and a method to either keep values or drop values…..fairly easy (lots of looping options and shortcuts) …. But…. not so fast on very big tables (mapbasic loops can be slow)

 

so i am all ears if there are other ways of doing this via say SQL.

 

 

regards

 

Russell

British Geological Survey

 

For UK geology data:

http://www.bgs.ac.uk/opengeoscience/home.html

 

For global geology data:

http://portal.onegeology.org/

For more options, visit this group at http://groups.google.com/group/mapinfo-l?hl=en.

 

 

 

[kompas]

Russell,
thank you for such a precise answer :) I was afraid this might turn
out like that :( Well, I'll go through MB then.
Jakub

On 9 Gru, 15:47, "Lawley, Russell S" <rs...@bgs.ac.uk> wrote:
> Hi kompass,
> I have had to do this on numerous occasions, , the method i use is to create a look-up table of all the sites and their geology intersections (using a similar sql to that given to Uk_dave):
>
> Select site.site_id, geoltab.geo_id from site, geoltab where site.Obj intersects geoltab.Obj order by site.site_id, geoltab.geo_id into mylookup
>
> I then use a bit of mapbasic code to crawl down thru the lookup table looking at the top n  records per unique site id (say  top 3)
>
> So eg  if mylookup looks like this, and i want the top three hits
>
> Site1, geol987 **
> Site1, geol88  **
> Site1, geol75  **
> Site1, geol6
> Site2, geol987 **
> Site2, geol68  **
> Site3, geol75  **
> Site3, geol6   **
> Site3, geol5   **
> Site3, geol3
>
> Then only the ** records get returned..Note that site 2 only has two hits to start with so can only return two hits
>
> In pseudo code terms you need  something that
>

> 1.      Saves the mylookup as a table in its own right, and then reopens it
> 2.      Requests an  n count value (say 3) via a dialog (or hard code)
> 3.      fetches the first record  of the lookup
> 4.      Reads the siteid in column 1  into an ID_variable  and sets the 'n'count to 1
> 5.      starts a loop  ( i use 'for i = 1 tableinfo(.. nrows)' ..but you could use whatever you prefer)
> a.      fetches the ith row,
> b.      reads the  site_id and geolval from column 1 and column2
> c.      checks to see if siteid is same as ID_variable
> i.      if TRUE
> 1.      check to see if ncount ≤ n
> a.      if true then that record is wanted  so save values or table row to new table  (or whatever you want)
> b.      if false then this record lies outside the nth range requested
> 2.      increment ncount
> ii.     if FALSE  (it musy be a new site)
> 1.      ncount = 1 (we know this from logic)

> a. so save values or table row to new table  (or whatever you want)

> 2.      reset so id_variable = the new siteid value
> 6.      next loop
>
> so, basically a loop, a counter, a comparator and a method to either keep values or drop values.....fairly easy (lots of looping options and shortcuts) .... But.... not so fast on very big tables (mapbasic loops can be slow)