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
