GeoNames Forum

Hi,

I am using postgres + postgis to find the nearest X locations to a given lat/long.

I imported the dump and created a geometry column using the following commands

Code:

 SELECT AddGeometryColumn( 'public', 'geoname', 'latlon_point', 4326, 'POINT', 2 );
 UPDATE geonameSET latlon_point = PointFromText('POINT(' || longitude || ' ' || latitude || ')',4326);
 CREATE INDEX latlon_point_index
   ON geoname
   USING gist
   (latlon_point);
 ALTER TABLE geonameCLUSTER ON latlon_point_index;

And then the following query to show me all entries within 5000 meters
Code:

 SELECT *
  FROM geoname
  WHERE st_dwithin(latlon_point, PointFromText('POINT(76.6497222 12.3072222)',4326),5000)

But the above code does not result in proper result.

So could some one answer the following queries ?

1) What SRID should I use to create the column ? ( so that I can use meter/km as the filtering criteria in distance calculation)
2) Is there anything wrong with the nearest neighbor code ?

Thanks,
Rakesh

I got some success with the following code

Code:

 SELECT *
  FROM place
  WHERE nearest_geom(latlon_point, ST_SetSRID(ST_MakePoint(77.6001262664795,12.9766239819362),4326),5000)
 
 where,
 
 CREATE OR REPLACE FUNCTION nearest_geom(geometry, geometry, double precision)
   RETURNS boolean AS
 'SELECT $1 && ST_Expand($2,$3) AND ST_Distance_Sphere($1, $2) < $3'
   LANGUAGE 'sql' IMMUTABLE;
 ALTER FUNCTION nearest_geom(geometry, geometry, double precision) OWNER TO postgres;

I have also created a gist index using
Code:

 CREATE INDEX latlon_place_index
   ON place  USING gist (latlon_point);
 ALTER TABLE place CLUSTER ON latlon_place_index;

But the query takes ~10secs. I am using Postgres 8.2 with the latest postgis version.

Could someone help me find what is causing the slowness in the query ? Also, is there any place where I can find how geonames database is setup ( and the reverse geocoding is done ), since the geoname's webservice is pretty fast

Thanks,
Rakesh

I read that ST_Distance_Sphere is very slow compared to st_distance.

So ideally I should be using
SELECT *
FROM geoname
WHERE st_dwithin(latlon_point, PointFromText('POINT(76.6497222 12.3072222)',4326),5000)

But the result is not proper due to the SRID that I choose ( 4326, coz 4326 causes st_distance to return in radians and not meters). So what is the SRID that I need to choose for the entire geoname dump ?

Hi Rakesh

Did you check the query execution plan for your query? I doubt it is using the index.
As we anyhow do some processing in the application level we calculate the exact meter values within the application and not on the database. We also combine the radius parameter with the maxRows parameter which translates to a 'limit' clause on the database level.

Cheers

Marc

Mark,

marc wrote:

Did you check the query execution plan for your query? I doubt it is using the index.

As mentioned earlier, I have 2 possible queries ( using ST_Distance or st_dwithin ).

Using ST_Distance_Sphere ( This gives the proper result but is very very slow )
Code:

 explain analyze SELECT * FROM place
 where
   latlon_point && ST_Expand(ST_SetSRID(ST_MakePoint(77.6001262664795,12.9766239819362),4326),5000) 
   AND ST_Distance_Sphere(latlon_point, ST_SetSRID(ST_MakePoint(77.6001262664795,12.9766239819362),4326)) < 5000
 
 "Seq Scan on place  (cost=0.00..66746.65 rows=767860 width=48) (actual time=3577.644..10785.164 rows=3 loops=1)"
 "  Filter: ((latlon_point && '0103000020E6100000010000000500000000000060663AB3C000000000067BB3C000000060663AB3C000000000FA94B340000000A099D5B34000000000FA94B340000000A099D5B34000000000067BB3C000000060663AB3C000000000067BB3C0'::geometry) AND (st_distance_sphere(latlon_point, '0101000020E6100000010000786866534080CFFD0E08F42940'::geometry) < 5000::double precision))"
 "Total runtime: 10785.225 ms"

Using st_dwithin
http://postgis.refractions.net/support/wiki/index.php?ExamplesFindNearby, this seems to be the right query ( it is very fast too). But the only issue is I don't know in what SRID should I pass the input lat / long
Code:

 explain analyze SELECT * FROM place
  WHERE st_dwithin(latlon_point, 'POINT(100312 102312)',5000)
 
 "Index Scan using latlon_place_index on place  (cost=0.00..8.81 rows=1 width=48) (actual time=0.025..0.025 rows=0 loops=1)"
 "  Index Cond: (latlon_point && '01030000000100000005000000000000000045F7400000000000C2F740000000000045F740000000000033FA400000000000B6F940000000000033FA400000000000B6F9400000000000C2F740000000000045F7400000000000C2F740'::geometry)"
 "  Filter: ((latlon_point && '01030000000100000005000000000000000045F7400000000000C2F740000000000045F740000000000033FA400000000000B6F940000000000033FA400000000000B6F9400000000000C2F740000000000045F7400000000000C2F740'::geometry) AND ('010100000000000000807DF8400000000080FAF840'::geometry && st_expand(latlon_point, 5000::double precision)) AND (st_distance(latlon_point, '010100000000000000807DF8400000000080FAF840'::geometry) < 5000::double precision))"
 "Total runtime: 0.092 ms"

So ideally I should be using the second query, but I am not sure about the input format ( SRID ) for the lat/long to the query. Could you explain on how you achieved the same ?

marc wrote:

As we anyhow do some processing in the application level we calculate the exact meter values within the application and not on the database.

I am curious on why u choose to do so in application since postgis distance function can return in mts ( assuming the SRID you choose has units in meter ) ?

marc wrote:

We also combine the radius parameter with the maxRows parameter which translates to a 'limit' clause on the database level.

Makes sense. I would also be putting a limit clause to the queries.

-Rakesh

I got it working finally!

I am using the following code
Code:

 SELECT * FROM geoname1
  WHERE st_dwithin(latlon_point, transform(GeomFromText('POINT(-92.120663 35.868436)',4326),2163),5000)
 order by ST_Distance(latlon_point, transform(GeomFromText('POINT(-92.120663 35.868436)',4326),2163))
 limit 10

So I am using 2163 as the SRID. Hope it helps others!

-Rakesh

Why do you use transform function?

If you do these select without transform function, you receive wrong answer.

My question is:
Why two of these selects are diffrent?
:
1. :
Code:

  
 SELECT  st_distance(
  transform(GeometryFromText('POINT(52.218 22.016)',4326),2163),
   transform(GeometryFromText('POINT(52.218 21.016)',4326),2163));

2:

Code:

 SELECT  st_distance(
  GeometryFromText('POINT(52.218 22.016)',2163),
   GeometryFromText('POINT(52.218 21.016)',2163));

rakeshxp,

Have you figured out what SRID to use in order to get metered distance? I have exactly same problem as you had... still researching...

Dong

here is the difference between them

SRID function does not transform the geometry is any way - it simply sets the projection the geometry that it's currently in. Use ST_Transform if you want to transform the geometry into a new projection.

Also the 4326 just indicate that it is in long lat, refer to spatial_ref_sys table that catalogs all spatial reference systems known to PostGIS and is used for transformations from one spatial reference system to another. So verifying you have the right spatial reference system identifier is important if you plan to ever transform your geometries.

ref for this post has been take from postgis documentation

Also here is a listing of the interesting one 4326 - WGS 84 Long Lat, 4269 - NAD 83 Long Lat, 3395 - WGS 84 World Mercator, 2163 - US National Atlas Equal Area,

if you cannot handle how to make your queries equivalent , just tell me ill write them for you, but i think you have enouf information with those to make them equivalent, this is pretty trivial when you understand my last post,

have a nice day