Spatial query for the Nearest location given a lat and long?

[bdbeames]

I'm completely new to this so I need some help writing a query.

What I have is two tables with weather station information: id, name, latitude, longitude, extra.

I am using google map where a user can click on a station from one of the tables, I obtain the $latitude and $longitude points of that location.  I now want to use these to find the nearest weather station in my other table based on the $latitude and $longitude point.

All that I need back from the query is the id and name of the nearest station.  Could someone help me write a simple query to do this.

My development environment is Linux/Apache/Postgres/php.

Thanks.

[Milo van der Linden]

Some parts of this message have been removed. Learn more about Nabble's security policy.

Use a function like this one:

select station_id, station_name, distance(stations_2.geom,(select geom from stations_1 where station_id = 34)) as dist from stations_2 where station_id <> 34 order by dist asc

hope this one speaks for itself, it returns a table ordered by distance, from the nearest to the furthest, process the result in a view or something.

Good luck!



bdbeames schreef:

I'm completely new to this so I need some help writing a query.

What I have is two tables with weather station information: id, name,
latitude, longitude, extra.

I am using google map where a user can click on a station from one of the
tables, I obtain the $latitude and $longitude points of that location.  I
now want to use these to find the nearest weather station in my other table
based on the $latitude and $longitude point.

All that I need back from the query is the id and name of the nearest
station.  Could someone help me write a simple query to do this. 

My development environment is Linux/Apache/Postgres/php.

Thanks.
  

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Rick Zoolker]

Some parts of this message have been removed. Learn more about Nabble's security policy.

Is this correct?  As far as I can tell, this will not give the desired results.  The coordinates are specified in lat/long and therefore the st_distance function will not return the number you expect (e.g. the distance between long 88 and long 87 will return 1, which is clearly incorrect).  Therefore you will need to either project the points using a SRID into a Cartesian plane (use ST_Transform() ) or else you will have to use the distance_spheroid function (and specify all the associated spheroid parameters for the earth).

Now I could be wrong.  I know in Oracle Spatial the distance calculation seems to automatically project if you've specified all the SRIDs correctly.  Maybe Postgis does this as well, but I don't think so.  Anyone else?????

On 8/11/07, Milo van der Linden <mlinden@...> wrote:

Use a function like this one:

select station_id, station_name, distance(stations_2.geom,(select geom from stations_1 where station_id = 34)) as dist from stations_2 where station_id <> 34 order by dist asc

hope this one speaks for itself, it returns a table ordered by distance, from the nearest to the furthest, process the result in a view or something.

Good luck!



bdbeames schreef:

I'm completely new to this so I need some help writing a query.

What I have is two tables with weather station information: id, name,
latitude, longitude, extra.

I am using google map where a user can click on a station from one of the

tables, I obtain the $latitude and $longitude points of that location.  I
now want to use these to find the nearest weather station in my other table
based on the $latitude and $longitude point.

All that I need back from the query is the id and name of the nearest

station.  Could someone help me write a simple query to do this. 

My development environment is Linux/Apache/Postgres/php.

Thanks.
  

--

Milo van der Linden skype: milovanderlinden mlinden@... milovanderlinden@... milo@... http://www.3dsite.nl

De informatie in dit bericht reflecteert mijn persoonlijke mening en niet die van een bedrijf of instantie. Aan de informatie kunnen geen rechten worden ontleend. Indien dit bericht onderdeel is van een forum, mailing-list of community dan gelden automatisch de bij het betreffende medium behorende voorwaarden. The information in this message reflects my personal opinion and not that of a company or public body. All rights reserved.If this message is contained in a mailing-list or community, the rights on the medium are automatically adapted.

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[bdbeames]

Ok I'm completely lost.

I did more research and I ran across some information about the SRID, but none of this makes sense to me.  I've never looked at a spatial query before.

Lets say that I lave the lat and long points 41.7833, -111.855.  I now have a table called table_1 with id, name, lat, long, extra.  This is a very large data base 1-2 T-Bites.  Weather stations from across the US that are updated every hour.  I want to find the nearest station to the given lat and long point.

Could you be more specific of how to go about this.  

I also found mention of a Distance function, but no documentation of how it works.  Could I use this to find the top 1-5 nearest stations.  If so, could someone given me an example of how to write the query.

This is a postgres database NOT MYSQL

Thanks

[Rick Zoolker]

There's a bunch of ways.  I'm still not sure if Milo's version will
work with a properly specified SRID, but I can't seem to be able to
make it work.  The quick and dirty method is to use the
distance_sphere() function in place of the distance() function in
milo's query.  It will give you the distance between two lat/long
points assuming the earth is a sphere.  Probably good enough for what
you're doing.  If you want something more accurate use the
distance_spheroid() function and specify the spheroid you want to use,
but my guess is that the distance_sphere() will be A-OK.
To be complete:
select id, name, distance_sphere(ST_POINT(table_1.lat,
table_1.long),ST_POINT(@lat, @long) ) as dist from table_1 order by
dist asc


On 8/13/07, bdbeames <bdbeames@...> wrote:

>
> Ok I'm completely lost.
>
> I did more research and I ran across some information about the SRID, but
> none of this makes sense to me.  I've never looked at a spatial query
> before.
>
> Lets say that I lave the lat and long points 41.7833, -111.855.  I now have
> a table called table_1 with id, name, lat, long, extra.  This is a very
> large data base 1-2 T-Bites.  Weather stations from across the US that are
> updated every hour.  I want to find the nearest station to the given lat and
> long point.
>
> Could you be more specific of how to go about this.
>
> I also found mention of a Distance function, but no documentation of how it
> works.  Could I use this to find the top 1-5 nearest stations.  If so, could
> someone given me an example of how to write the query.
>
> This is a postgres database NOT MYSQL
>
> Thanks
> --
> View this message in context: http://www.nabble.com/Spatial-query-for-the-Nearest-location-given-a-lat-and-long--tf4253824.html#a12129033
> Sent from the PostGIS - User mailing list archive at Nabble.com.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@...
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Regina Obe]

I think like someone mentioned you will want to transform your data to another projection besides lat long to take advantage of indexes or if your data set is small enough, you can just use distance_sphere.  What was left out is you need a limit clause otherwise you are returning the whole table.

So first - you want to convert your lat long to a geometry like described here  -

http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut03

 

For your particular dataset SRID - 2163 (US National Atlas Equal Area meters)  might be good enough for your needs

 (4326  I think is more common than the 4269 mentioned in the article - I forget the difference)

 

Next if you are keeping your data in long lat (4326) then you can do the following

For example if this is in PHP it would be something like

//set $lon and $lat variables from posted input here

if(is_numeric($lon) && is_numeric($lat)){

$sql = "SELECT ws.*

FROM weatherstations ws

ORDER BY distance_sphere(ws.the_geom, setsrid(makepoint($lon, $lat),4326)) LIMIT 5";

//execute statement goes here

}

The above would be really slow if you have a large data set which it sounds like you do.  Lets say you created a geometry field in meters SRID 2163 and index it as described in above article, then you can  use the expand function to utilize indexes.  Here I assume the field is called the_geom_m and the distance between the a close station is no further than 10000 meters away

 

$sql = "SELECT ws.*

FROM weatherstations ws

WHERE expand(transform(setsrid(makepoint($lon, $lat),4326), 2163), 10000) && ws.the_geom_m

ORDER BY distance(ws.the_geom_m, transform(setsrid(makepoint($lon, $lat),4326), 2163)) LIMIT 5";

This technique is described here

http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor

 

And a more advanced technique using expanding expanding boxes when you want to do multiple simultaneous nn searches or want to set your expand box really really high with minimum penalty is here

http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor_generic

 

Hope that helps,

Regina

 

 

-----Original Message-----
From: postgis-users-bounces@... [postgis-users-bounces@...] On Behalf Of bdbeames
Sent: Monday, August 13, 2007 12:05 PM
To: postgis-users@...
Subject: Re: [postgis-users] Spatial query for the Nearest location given alat and long?


Ok I'm completely lost.

I did more research and I ran across some information about the SRID, but
none of this makes sense to me.  I've never looked at a spatial query
before.

Lets say that I lave the lat and long points 41.7833, -111.855.  I now have
a table called table_1 with id, name, lat, long, extra.  This is a very
large data base 1-2 T-Bites.  Weather stations from across the US that are
updated every hour.  I want to find the nearest station to the given lat and
long point.

Could you be more specific of how to go about this. 

I also found mention of a Distance function, but no documentation of how it
works.  Could I use this to find the top 1-5 nearest stations.  If so, could
someone given me an example of how to write the query.

This is a postgres database NOT MYSQL

Thanks
--
View this message in context: http://www.nabble.com/Spatial-query-for-the-Nearest-location-given-a-lat-and-long--tf4253824.html#a12129033
Sent from the PostGIS - User mailing list archive at Nabble.com.

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

---

The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[bdbeames]

ok

I'm starting to get somewhere with this thing, but it is still returning incorrect values.  I got this working before I read the last two post.  I'll try your ideas, and see if they help.  

This is what I have so far, but it is returning the wrong values.  I'm assuming because the query does not take into account that earth is a sphere.  

SELECT id,name
FROM coop.stations
ORDER BY Distance(GeomFromText('POINT(-111.855 47.7833)') ,
GeomFromText('POINT('||coop.stations.longitude||' '||coop.stations.latitude||')')) asc limit 5;

Feel free to point out what I'm doing wrong.

Thanks

[bdbeames]

Results

Ok, I think it is working.

Here what I got for future reference

Given a $latitude and $longitude point I query table_1 to find the closest/nearest location points.

SELECT id, name,
   distance_sphere(GeomFromText('POINT('|| $longitude ||' '|| $latitude ||')'),
   GeomFromText('POINT('|| table_1.longitude ||' '|| table_1.latitude ||')')) as dist
FROM table_1
WHERE active = 't'
ORDER BY dist LIMIT 5;

Thanks again for all the help

[Rick Zoolker]

Looks like you got it.  FYI, ST_Point($longitude, $latitude) will work
slightly faster than using the GeomFromText().  Also, as Regina
mentioned, you won't be making use of any spatial indicies here, so
the query will run slower than what would be possible.

On 8/13/07, bdbeames <bdbeames@...> wrote:

>
> Results
>
> Ok, I think it is working.
>
> Here what I got for future reference
>
> Given a $latitude and $longitude point I query table_1 to find the
> closest/nearest location points.
>
> SELECT id, name,
>    distance_sphere(GeomFromText('POINT('|| $longitude ||' '|| $latitude
> ||')'),
>    GeomFromText('POINT('|| table_1.longitude ||' '|| table_1.latitude
> ||')')) as dist
> FROM table_1
> WHERE active = 't'
> ORDER BY dist LIMIT 5;
>
> Thanks again for all the help
> --
> View this message in context: http://www.nabble.com/Spatial-query-for-the-Nearest-location-given-a-lat-and-long--tf4253824.html#a12133535
> Sent from the PostGIS - User mailing list archive at Nabble.com.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@...
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Milo van der Linden]

Some parts of this message have been removed. Learn more about Nabble's security policy.

I agree.

In preparing my sample nearest function for this post, I assumed that the points mentioned where in spatial_columns, hence making best use of indices and all.
My advice would be to create a geometry column for this table and convert the lat/lon columns to one spatial column. That's where postGIS will be at it's best, not in storing doubles in two columns.

Rick Zoolker schreef:

Looks like you got it.  FYI, ST_Point($longitude, $latitude) will work
slightly faster than using the GeomFromText().  Also, as Regina
mentioned, you won't be making use of any spatial indicies here, so
the query will run slower than what would be possible.

On 8/13/07, bdbeames bdbeames@... wrote:
  

Results

Ok, I think it is working.

Here what I got for future reference

Given a $latitude and $longitude point I query table_1 to find the
closest/nearest location points.

SELECT id, name,
   distance_sphere(GeomFromText('POINT('|| $longitude ||' '|| $latitude
||')'),
   GeomFromText('POINT('|| table_1.longitude ||' '|| table_1.latitude
||')')) as dist
FROM table_1
WHERE active = 't'
ORDER BY dist LIMIT 5;

Thanks again for all the help
--
View this message in context: http://www.nabble.com/Spatial-query-for-the-Nearest-location-given-a-lat-and-long--tf4253824.html#a12133535
Sent from the PostGIS - User mailing list archive at Nabble.com.

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

    

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

  

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users