Help with nearest location
От | Paul Jones |
---|---|
Тема | Help with nearest location |
Дата | |
Msg-id | 1391815990.83296.YahooMailNeo@web161701.mail.bf1.yahoo.com обсуждение исходный текст |
Ответы |
Re: Help with nearest location
|
Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления: