Selecting geograpghic (.obj) information from multiple tables using SQL
KateW
indicates it can be done, but I can't work it out and having spent
half an hour trying to search the archive of the MapInfo-L but I've
had no luck finding out more.
I want to select environmental information from multiple tables where
they intersect a site boundary.
MapInfo help says
"Where Condition
This field serves different purposes, depending on the nature of your
query. When you are querying a single table, the Where Condition field
is optional. If your query involves joining two *or more tables*, you
must specify a Where Condition expression, and that expression must
identify how MapInfo Professional should join the two tables"
The tables I'm searching are MN25M_500m_buffer, Cumbria_LPO,
Wildlife_Sites_Updated, SPA, SSSI
I want to use the MN25M buffer polygon to select other sites which
intersect it. This works with two tables (e.g. MN25M_500m_buffer.Obj
Intersects Cumbria_LPO.Obj).
However when I ask it to look further for tables
Wildlife_Sites_Updated, SPA and SSSI in the same query using the Or
operator it falls over saying "No join specified between tables
MN25M_500m_buffer and Cumbria_LPO"
What do I need to do to get this to work? How do I go about joining
all the tables I want to search? I could do with a worked example as
this is missing from any of the help information!
Many thanks in advance,
Kate Willshaw
Planning and Policy Officer
Cumbria Wildlife Trust
kat...@cumbriawildlifetrust.org.uk
Data Directions
Please post your actual "where" condition and we'll see what's up.
One thing to consider is precedence in MapInfo, which basically defines
which operations occur first, and in some instances you may require
parenthesis around "Or" expressions.
The precedence of MapInfo operators are:
Highest priority
parentheses
exponentiation
negation
multiplication, division, Mod, integer division
addition, subtraction
geographic operators, comparison operators
Like
And
Or = Lowest Priority
Regards,
Bill
==================================================================
Many thanks in advance,
Kate Willshaw
--
You received this message because you are subscribed to the Google Groups
"MapInfo-L" group.To post a message to this group, send email to
mapi...@googlegroups.com To unsubscribe from this group, go to:
http://groups.google.com/group/mapinfo-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
http://groups.google.com/group/mapinfo-l?hl=en
KateW
Thanks for replying
The Where Condition currently reads:
MN25M_500m_buffer.Obj Intersects Cumbria_LPO.Obj Or
MN25M_500m_buffer.Obj Intersects Wildlife_Sites_Updated.obj Or
MN25M_500m_buffer.Obj Intersects SPA.obj Or MN25M_500m_buffer.Obj
Intersects SSSI.obj
The sytax is correct according to the verify checker, but this comes
up with the error "No join specified between tables MN25M_500m_buffer
and Cumbria_LPO"
I am not a regular SQL user and am probably doing something very
obviously wrong as I'm not the best at this sort of thing.
Kate
On Jun 9, 12:37 pm, "Data Directions" <b...@datadirections.com.au>
wrote:
Data Directions
Even though geographic operators have higher precedence that the "Or"
portion, it wouldn't hurt to add parentheses and see what happens.
Try this:
(MN25M_500m_buffer.Obj Intersects Cumbria_LPO.Obj) Or (MN25M_500m_buffer.Obj
Intersects Wildlife_Sites_Updated.obj) Or (MN25M_500m_buffer.Obj Intersects
SPA.obj) Or (MN25M_500m_buffer.Obj Intersects SSSI.obj)
Maybe MapInfo is just being finicky?
Cheers,
Bill
============================================================================
===============
Data Directions
The other problem could be that the table structure (ie. Field names and
data types) differs between the tables ???
If so, you'll probably have to run each "intersect" expression separately
(ie 4 times) and then merge the final result (after modifying the table
structure so that it is uniform between all tables).
If neither of these two suggestions works, I'll have to let another user
propose solutions.
Regards,
Bill
============================================================================
======
KateW
(MN25M_500m_buffer.Obj Intersects Cumbria_LPO.Obj) Or
(MN25M_500m_buffer.Obj Intersects Wildlife_Sites_Updated.obj) Or
(MN25M_500m_buffer.Obj Intersects SPA.obj) Or (MN25M_500m_buffer.Obj
Intersects SSSI.obj)
MN25M_500m_buffer
and Cumbria_LPO." with the addition of "Invalid join condition in
WHERE clause"
Really thought this should be possible considering what the MapInfo
help says about multiple tables.
On Jun 9, 12:56 pm, "Data Directions" <b...@datadirections.com.au>
KateW
field names and structures. The help says "Geographic operators allow
you to select objects on the basis of their spatial relationship to
some other object."
I have 200 odd sites for which I need to search a number of
environmental variables held in many different tables (the ones l have
listed are just an example, there are probably about ten more tables
listing things like species and habitats). This may take some time if
I have to run each query seperately....!!!
On Jun 9, 1:03 pm, "Data Directions" <b...@datadirections.com.au>
Uffe Kousgaard
I am quite sure you will have to create a temporary table with all objects from Cumbria_LPO,
Wildlife_Sites_Updated, SPA and SSSI. Finally query that one instead of using OR.
Regards
Uffe Kousgaard
Thomas Bacon
Bill's absolutely right with this. If the table structures of the joined tables (in this case Cumbria_LPO, Wildlife_Sites_Updated, SPA, and SSSI) have different table structures (which I'm assuming they do as they look to be from different sources and of varying data) then a join will not work.
You can get it to work, though it may not yield the results you're after (not sure exactly what data you need to carry over from these tables, if it's just a geographic lookup then this should work fine) using the following process:
-add an ID field of a common data type (character) to the tables Cumbria_LPO, Wildlife_Sites_Updated, SPA, and SSSI
-update the ID field in each table with: "tablename_" & RowID
-perform a query on each table selecting out only the ID field and then save the result as a separate table
-close the queries and open the saved query tables as well as MN25M_500m_buffer
-carry out the geographic join on the saved query tables
You can then use the ID field in the results to lookup any required data from the original tables should you need it.
Hope this helps,
Tom Bacon
Graduate GIS Engineer
Asset Management South
Tel: 01444 472380
www.mouchel.com
Kate,
Regards,
Bill
============================================================================
======
Hi Bill
Thanks for replying
Kate
The information in this e-mail is confidential and may be legally privileged. It is intended solely for the addressee. Access to this email by anyone else is unauthorised. Any views or opinions expressed in this e-mail may be solely those of the author and are not necessarily those of Mouchel. Mouchel Limited, Registered in England at Export House, Cawsey Way, Woking, Surrey, UK, GU21 6QX Registered No : 1686040
KateW
Thanks for your help, I will give this a try.
Best wishes
Kate
Spencer Simpson
in a fully-implemented SQL.
What you're trying to do is called a "UNION" query, which unfortunately
doesn't exist within the subset of SQL contained within MapInfo Pro.
Thomas and Uffe have set you on the right path, which is to combine all of
the tables you're querying the buffers against into one table, and query
against that.
This makes sense semantically, because at some abstract level, you're
regarding all of the records in all of the tables as the same kind of thing.
________________________________
Spencer
-----Original Message-----
From: KateW [mailto:kwil...@googlemail.com]
Sent: Thursday, June 09, 2011 7:50 AM
To: MapInfo-L