[GIS] PostGIS Geography Query not working

postgis

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!

Best Answer

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.

Related Question