SQL to select records from one table that intersect objects from a second table, without duplicates

68 views
Skip to first unread message

Nick Lawrence

unread,
Jan 3, 2017, 1:24:06 AM1/3/17
to MapInfo-L
Hello all,

What is the SQL to select records from one table that intersect objects from a second table, without duplicates?

For example;
table1 is mapsheets (regions)
table2 is roads (polylines)

I want to select records from table1 that intersect records from table2, without duplicates.

My problem is that my SQL does return duplicates, that is, if a mapsheet intersects two roads, the mapsheet is returned twice. But I only want the mapsheet to be returned once.

I can accomplish my goal by running the "Delete Duplicate" tool afterwards, but I was hoping for a SQL that accomplishes this in one step.

Ta,
Nick Lawrence



Peter Horsbøll Møller

unread,
Jan 3, 2017, 2:26:05 AM1/3/17
to mapi...@googlegroups.com

If your road tables has a column with unique IDs you can use a combination of a sub select and a grouped join:

 

Select * from ROADS

Where ID In

   (

   Select ROADS.ID

      From ROADS, MAPSHEETS

      Where ROADS.OBJ Intersects MAPSHEETS.obj

      Group By ROADS.ID

   )

   into Selection

 

I must admit this is the first time I have tried it so I wasn’t sure if MapInfo Pro supported a join in a sub select, but it does.

 

Peter Horsbøll Møller

EMEA Channel Enablement Specialist

Location Intelligence | MapInfo

 

M: +45 29 133 769

peter....@pb.com | @phorsbollmoller

pitneybowes.com/dk | mapinfo.com

--
--
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

---
You received this message because you are subscribed to the Google Groups "MapInfo-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mapinfo-l+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.




Reply all
Reply to author
Forward
0 new messages