[GIS] Distance from point stored in geometry with SRID 4326

distancegeographygeometrypostgissrid

I have stored 2.5 million data points (addresses) using the geometry data type in column latlong with SRID 4326. The latlon – column has a GIST index defined. I would select all addresses within a distance given in metres.

I understand that SRID 4326 measures distance in degrees, so it doesn't make sense in my case. However the query

select *
from adresse
where st_distance(latlong::geography, ST_SetSRID(ST_Point(16.520, 47.846), 4326)::geography) < 200

is incredibly slow, I guess it is unable to use the index.

How can I speed up this query?

Best Answer

You want to use ST_DWithin() which takes advantage of indexing

SELECT *
FROM adresse
WHERE ST_DWithin(latlong::geography, ST_SetSRID(ST_Point(16.520, 47.846), 4326)::geography, 200)
ORDER BY ST_Distance(latlong::geography, ST_SetSRID(ST_Point(16.520, 47.846), 4326)::geography);

Related Solutions

[GIS] PostGIS – table with 3d points with fast ordering by distance

<-> is not supported for 3D indexes (though it looks like you have a 2-d index so it should be doing a 2D check).

Anyway use ST_3DDwithin to use an index

SELECT * FROM (select * from db_testpoints WHERE ST_3dDwithin(point,ST_geomfromewkt('POINT(0 0 0)'),somevalue_that_will_guarantee_100_records) ) As foo 
ORDER BY ST_3DDistance(point,ST_geomfromewkt('POINT(0 0 0)'));

[GIS] How to optimize this ST_Distance query

You're mixing units in your original attempt, and improperly using geography in your second. I'll provide a geography solution, though if your data are local the very highest performance will be using planar (not 4326) geometry.

-- Change the data type in the tables
ALTER TABLE powerplants_us_201603 ALTER COLUMN geom TYPE geography USING geography(geom);
ALTER TABLE parcels ALTER COLUMN geom TYPE geography USING geography(geom);
ALTER TABLE powerplants_us_201603 RENAME COLUMN geom TO geog;
ALTER TABLE parcels RENAME COLUMN geom TO geog;

-- Build real geography indexes
CREATE INDEX parcels_geog_x ON parcels USING GIST (geog);
CREATE INDEX powerplants_geog_x ON powerplants_us_201603 USING GIST (geog);

-- Run the (simpler now) query
SELECT DISTINCT ON (g1.gid)
  g1.*, 
  g2.plant_name AS closest_utility_name,
  g2.primsource AS closest_utility_type,
  round(ST_Distance(g1.geog, g2.geog) / 1609.34) AS closest_utility_miles
FROM parcels AS g1, 
JOIN powerplants_us_201603 AS g2
ON ST_DWithin(g1.geog, g2.geog, 100 * 1609.34)
ORDER BY g1.gid, closest_utility_miles;

Best Answer

Related Solutions

[GIS] PostGIS – table with 3d points with fast ordering by distance

[GIS] How to optimize this ST_Distance query

Related Question