[GIS] How to cast geom to geog in PostGis to find points within a given distance

postgis

I have a Geo table with geometry column storing point data SRD=4326

I need to find all points within a given distance in meters of a Longitude, Latitude

Do I create a geography column from the geometry column or the Longitude, Latitude stored in the table

Do the conversion on the fly within the query

Could someone suggest how to perform both options and there pro & cons?

Best Answer

You can do it both ways, storing geography or storing geometry and convert on the fly. If you create a geom column not based on SRID 4326, you can convert it to a geography type before the computation as follows (using the ST_DWithin function)

SELECT geom FROM my_points WHERE ST_DWithin(geom::geography, my_latlon);

Of course, this second approach is slower in use because of the need to cast geom to geography type.

Related Solutions

[GIS] How to find nearest line from point using PostGIS

SELECT name,ref,type,ST_Distance(ST_Buffer(r.geom,20),ST_SetSRID(ST_MakePoint(lon, lat),4326)) 
FROM roads r 
ORDER BY 4 ASC 
LIMIT 1;

For point in lon/lat coordinates:

SELECT name,ST_Distance(r.geom,ST_SetSRID(ST_MakePoint(lon, lat),4326)) FROM roads r ORDER BY 2 ASC LIMIT 1;

[GIS] PostGIS – Geometry or Geography find points within distance in meters

The definition of SRID 4326 is in fact IN DEGREES. ALso as it says in the ST_DWithin page:

Returns true if the geometries are within the specified distance of one another. For geometry units are in those of spatial reference and For geography units are in meters and measurement is defaulted to use_spheroid=true (measure around spheroid),

Since you're are using the 4326 SRID, you can cast from geometry to geography and vice versa like this :

select st_distance(<geom_column>::geography) from myTable;

Most of the functions in postgis are overloaded, meaning, they return values and run a different procedure based on the input data type.

eg:

boolean ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid);

boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters);

they have the same name, but the first function returns T/F based on units of the srid, and the second returns T/F based on meters.

If you plug into the function geog types, for its calculation, will take into account the curve of the earth so the algorithm 'costs' a bit more.

If you want to check how much more it costs, you can run for each query an EXPLAIN-ANALYSE

Best Answer

Related Solutions

[GIS] How to find nearest line from point using PostGIS

[GIS] PostGIS – Geometry or Geography find points within distance in meters

Related Question