Re: Help with nearest location

On 08/02/14 12:33, Paul Jones wrote:
> Using PostgreSQL 9.3.2 and PostGIS 2.1.0.
>
> Can someone explain the best way to perform an operation such as finding the nearest
> fire station to a fire?  Given that we have a fire at POINT(-87.638 41.8140) and:
>
>          CREATE TABLE firestations
>          (
>                  name            VARCHAR,
>                  location        VARCHAR,
>                  latlong         GEOMETRY
>          );
>
>          \copy firestations from stdin csv
>          E119,"6030 N AVONDALE AVE CHICAGO,IL 60631",0101000000E6AE5E741FF355C01C284FDBDFFE4440
>          E121,"1724 W 95TH ST CHICAGO,IL 60643",010100000044D79DDD9BEA55C0F9E9FED051DC4440
>          E80,"12701 S DOTY AVE CHICAGO,IL 60633",01010000000E4F5159CCE555C02C548D14D0D44440
>          E1,"419 S WELLS ST CHICAGO,IL 60607",01010000004519979788E855C03515A05722F04440
>          E14,"1129 W CHICAGO AVE CHICAGO,IL 60642",010100000006DBC095FFE955C01B87FE5FAFF24440
>          E49,"4401 S ASHLAND AVE CHICAGO,IL 60609",01010000002D94A0E68EEA55C022EDCA8832E84440
>          E54,"7101 S PARNELL AVE CHICAGO,IL 60621",0101000000BDB211FFD9E855C0547A1607F4E14440
>          E73,"8630 S EMERALD AVE CHICAGO,IL 60620",0101000000F9B5F3FC1DE955C0590854E953DE4440
>          E34,"4034 W 47TH ST CHICAGO,IL 60632",0101000000067CA2F663EE55C093AF6C4669E74440
>          E93,"330 W 104TH ST CHICAGO,IL 60628",01010000004D100BC979E855C05C68056F45DA4440
>          E86,"3918 N HARLEM AVE CHICAGO,IL 60634",0101000000B4284F7BA9F355C0FCAFAE09BFF94440
>          \.
>
> I can easily find the least distance to the fire with:
>
>          SELECT min(ST_Distance('POINT(-87.638 41.8140)',latlong)) FROM firestations;
>
> but I can't include the name of the station in that select.  If I try grouping by station name,
> I get all the stations:
>
>          SELECT name,min(ST_Distance('POINT(-87.638 41.8140)',latlong))
>                  FROM firestations GROUP BY name;
>
> This query gives the answer I want, but I'm wondering if there is a solution using
> the 'min' aggregate function.
>
>          SELECT name,location, ST_Distance('POINT(-87.638 41.8140)',latlong) dist
>                  FROM firestations ORDER BY dist LIMIT 1;
>
> Thanks,
> Paul Jones
>
>
I thought Fire engines had to follow roads, therefore could not fly
through hills and buildings etc.!

Also that some routes may be faster than others due to traffic and/or
other considerations, so the shortest route may not necessarily be the
fastest...


Cheers,
Gavin