Mapinfo SQL Query - Calculate the distance and Selection of object from Table A & update Table B based on the search ring distance mentioned in table B

[Hemdatt Sharma]

Hi

I am new in this group and understand simple sql queries. I am trying to do a complex selection task using 2 different tables using SQL Query as following:

Setup:
1. Windows 10
2. Mapinfo Pro Version 17.0

Mapinfo Data Tables:

1. Table A & Table B
2. Common database ID/Key - None
3. Table B contains a column with "search ring distance" in meters

Requirement:
1. Calculate the distance of Table B point from nearest Table A point
2. Select the objects from Table A, which are falling under the "search ring distance" specified  in "Search ring distance" from Table B.
3. Populate Table B with the ID of Table B point falling under "Search Ring Distance" and the "Distance" in meters

I tried to describe the problem, pls do let me know if some other information is needed.

Please help

Thanks
Hemdatt

[Peter Horsbøll Møller]

I think you can get pretty far by using the Distance Calculator tool.

It will calculate the distance between all points in one table to one or more points in another table.

 

The result can be saved into a new table holding the name/id from the two input tables and the distance between the points.

This also means that you need to create a column with a unique ID for your two tables if they don’t have this already.

You can add a new column of type integer to the tables and update this column with the ROWID (which will assign a counter to each record)

 

When you have created the result from the Distance calculator you can link the result back to the base table via a join on the Unique ID column:

Select *

From TableB, DistanceTable

Where TableB.B_ID = DistanceTable.B_ID

And TableB.SearchRingDistance < DistanceTable.Distance

 

The result from the above query should be the records from table B that have a records with their search distance in TableA.

 

Peter Horsbøll Møller

www.precisely.com

 

—

Peter Horsbøll Møller

Principal Sales Engineer - Distinguished Engineer

Precisely.com

 

From: mapi...@googlegroups.com <mapi...@googlegroups.com> On Behalf Of Hemdatt Sharma
Sent: 22. december 2021 07:49
To: MapInfo-L <mapi...@googlegroups.com>
Subject: [MI-L] Mapinfo SQL Query - Calculate the distance and Selection of object from Table A & update Table B based on the search ring distance mentioned in table B

 

This message originated Externally. Use proper judgement and caution with attachments, links, or responses.

 

--
--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/mapinfo-l/be8ef16e-fbe1-4315-beff-b8efe011765an%40googlegroups.com.

[Hemdatt Sharma]

Thanks Peter

So far I am using distance tool only. Using distance tool is time consuming while working with large database. So I am exploring SQL query option.

[Ian Oldfield]

Hemdatt, this response, like me, is a bit old!

I created a point table (Table A) and created a number of points (39) and update the default field (ID) with the rowID.   I then created a new point table (Table B with 7 points) and likewise updated the ID field with the row number.  I also added two fields to TableB: SearchDistance, INT and TableA_ID INT.   I then created buffers in Table B using SearchDistance (in metres) as the buffer size.  Finally ran a query where TableA.obj is within TableB.obj.  Displayed TableB_ID, Table_B_SearchDistance, Table_A.ID.   This gave me a query result where TableA objects were within TableB.SearchDistance.   Not sure this is what you need but here's what I got:

Apologies if this insults your knowledge.

Kind regards

Ian.

[Hemdatt Sharma]

Hi Ian

Thanks for your response and solution.

I referred to some of the posts in this group alongwith the Mapbasic documentation and figured it out as following.

Select 

TableA.Serial#, TableB_Serial#, SphericalObjectDistance(TableB.obj, TableA.obj,"m") "Distance_m" 

From  TableB, TableA 

Where (TableB.ID = TableA.ID) and SphericalObjectDistance(TableB.obj,TableA.obj,"m") <=TableB_SearchRing 

Into  qrySelection

Browse * From qrySelection

Regards

Hemdatt

To view this discussion on the web visit https://groups.google.com/d/msgid/mapinfo-l/06e9b0e9-6660-4719-9fc0-705fb889deb2n%40googlegroups.com.

--

Thanks and Regards

Hemdatt Sharma
+91 9136001429
hemdat...@gmail.com

[Ian Oldfield]

Hi Hemdatt,   see..... you can teach an old dog new tricks!    Thank you for sharing your rather more elegant solution.  Ian.