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