D
D
Danil Chekalin2016-05-05 14:34:59
PostgreSQL
Danil Chekalin, 2016-05-05 14:34:59

Why does Postgres (PostGis) not correctly calculate occurrences of points in a radius?

There is a table with this DDL:

CREATE TABLE buildings
(
    id INTEGER PRIMARY KEY NOT NULL,
    city VARCHAR(255) NOT NULL,
    street VARCHAR(255) NOT NULL,
    build_number DOUBLE PRECISION NOT NULL,
    location GEOMETRY(POINT,4326) // пробовал и GEOGRAPHY(POINT,4326)
);

There are 100 entries in the table, each location field has a value:
sprintf('SRID=4326;POINT(%s %s)', $faker->longitude(55, 55.08), $faker->latitude(82.93, 83.01));

That is, the points are concentrated in one place not far from each other.
Now displaying a point on the map:
5e12612300ce48ccb028bbd4d0cf6908.jpg
Left map, this is the output of all points. The right map has a red dot in the radius of which the search will take place.
The red dot is at the coordinate
55.0502, 82.9615

We perform a search with a radius of 500m. Request:
SELECT *
FROM "buildings"
WHERE ST_DWithin(location, ST_GeomFromText('POINT(55.0502 82.9615)'), 500, TRUE)

Result:
b581aa07a68b43738d6aad7f9377be9d.jpg
We perform a search with a radius of 1500m. Request:
SELECT *
FROM "buildings"
WHERE ST_DWithin(location, ST_GeomFromText('POINT(55.0502 82.9615)'), 1500, TRUE)

Result:
6842dc1c4b2843b3abb92c75fb729658.jpg
Result with both maps on a smaller scale.
819574e6dc8140cfad5ec98d8e0efe36.jpg

Answer the question

In order to leave comments, you need to log in

1 answer(s)
R
Rsa97, 2016-05-05
@dakiesse

You are using ST_DWithin with four parameters, this is Geography mode. For it, objects must have the same SRID. Try to remove the fourth parameter or create a point with an explicit SRID
ST_GeomFromText('POINT(55.0502 82.9615)', 4326)

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question