MapInfo SQL Distance Query
kewlgolf
I would consider myself fairly capable when it comes to SQL queries
using MapInfo but have hit a road block with this one certain
request.
I have two sets of data: Communities, and hospitals. There are around
400 communities, and 7 hospitals.
I want to find out the distance from each community's centroid to the
nearest hospital.
I realize I must use the Distance function within SQL, but believe
there may be a subquery I am missing somewhere. When I proceed to
execute my query it only returns 7 records (only those communities
which contain hospitals). This is obviously a problem with the "Where"
clause, but I am stuck as to how to get distances for all communities
to hospitals and not just those communities which contain hospitals.
I hope this isn't to confusing and appreciate any help!
Thanks
Lars I. Nielsen (GisPro)
What you seek is an outer or cross join, where every record in one table is matched to every record in another table.
This is not possible per-se in the MapInfo SQL subset, which only supports inner joins.
But you can mmic an outer/cross join by applying a simple work-around :
1. Add an integer column to both tables, e.g. named dummy, and leave them with their default value of zero.
2. Perform an inner join on these two columns, e.g. A.dummy = B.dummy. That'll give you a result table of 7 x 400 record matches.
3. Add the necessary functions and additional filters as necessary.
HTH
Best regards / Med venlig hilsen Lars I. Nielsen GIS & DB Integrator GisPro
kewlgolf
I hadn't thought of that.
Much appreciated
On Jul 18, 3:58 pm, "Lars I. Nielsen (GisPro)" <L...@gispro.dk> wrote:
> Hi,
> What you seek is an outer or cross join, where every record in one table is matched to every record in another table.
> This is not possible per-se in the MapInfo SQL subset, which only supports inner joins.
> But you can mmic an outer/cross join by applying a simple work-around :
> 1. Add an integer column to both tables, e.g. named dummy, and leave them with their default value of zero.
> 2. Perform an inner join on these two columns, e.g. A.dummy = B.dummy. That'll give you a result table of 7 x 400 record matches.
> 3. Add the necessary functions and additional filters as necessary.
manymiles
As I have followed this thread, I was pleased to see it come to a resolution that met the needs to resolve the problem and expose a way to use a strength of MapInfo Pro.
I have recently been exploring the Envinsa On Line Services (EOLS) which are offered on a trial basis with each copy of Pro and then at reasonable subscription rates. I used the Drive Time Module to solve a problem like this with actual drive time (or drive distance) information.
In my case, I was trying to determine the typical commute times to various zip codes from specific office parks. I used the EOLS drive time module to create concentric drive time 1 rings out to 40 minute commute time. I then joined the ring table and the zip code boundary table in a sql query that returned minimum and maximum times to the intersecting zip code features. Did a similar process on zip centroids. The resulting matrix from pulling it all into one browser was much more informative than the matrix that the client had done using point to point routing from office to centriod using Google maps.
Just at suggestion, look at the EOLS modules, potentially a reasonable approach to geocoding and distance determination needs.
Jim
James Henry
JCH GeoInfo Solutions
N 36 01.802 W 078 56.292
--
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