2

I’m struggling with returning data from the following query. It should be returning all data within a 5k radius of a given Longitude, Latitude point.

SELECT stuff 
    FROM MyGeoTable 
    WHERE ST_DWithin(ST_GeographyFromText(‘SRID=4326;POINT(LONG,LAT)’), geog,5000) 

It is not returning all data it should be best guess there is an issue with the Geography column this was created from a geometry column (type Geometry) and is a collection of lines and polygons all on WGS84.

I tried to recreate the Geography column thus

UPDATE MyGeoTable SET geog = (ST_Transform(geom,4326));

This gives

ERROR: Geometry type (LINSTRING) dose not match column type (Polygon)

My question is twofold

  1. how can recreate the geography column, and check it has worked on every row?

  2. If this is not possible How might perform a similar query just using the geometry the 5k radius doesn't have to be exact but is from a user input!

7

First, add a generic geography column (since you have a mixture of lines and polygons). Ha ha, yes, the syntax below is correct!

ALTER TABLE mytable ADD COLUMN geog geography(geometry);

Then add the data to the column:

UPDATE mytable SET geog = ST_Transform(geom, 4326);

Then add an index:

CREATE INDEX mytable_geog_idx ON mytable USING GIST (geog);

Now your query should work:

SELECT * FROM mytable
WHERE ST_DWithin('POINT(%LON %LAT)'::geography, geog, 5000);

If your data is in a projected coordinate system, you should just skip all this nonsense and use the geometry column.

  • I will attempt your suggestions In the morning!! In the mean time How Do I 'skip all this nonsense and use the geometry column.'? – Holly Feb 17 '16 at 23:22
  • If your data is already projected, then a simple: SELECT * FROM mytable WHERE ST_DWithin('...', geom, 5000) will work. – Paul Ramsey Feb 18 '16 at 17:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.