1

I have a table called properties (generated with EF Core) which has a column called GeoPoint. In the database, this column is a geomerty(Point) type. All properties are located within Hungary. I am trying to create a procedure for search properties within a distance of a specific coordinate.

Distance should be in meters.

Properties table has two text columns, longitude, latitude, which I have used to populate this new extra column called GeoPoint and I have used the following script to do this.

UPDATE "Properties" SET "GeoPoint" = (ST_MakePoint(CAST("Longitude" AS double precision), CAST("Latitude" AS double precision)))

After this I had all my GeoPoint columns populated, but not sure if this was the correct way.

To search I tried to use the following script

SELECT p."Id", p."Longitude", p."Latitude", p."GeoPoint" 
FROM "Properties" p
WHERE ST_DWithin(
        p."GeoPoint",
        ST_Transform(
            ST_GeomFromText(
                'POINT(19.081750000000 47.54770000000)',
                4326),
            2100),
        5000)

and I get the following error;

ERROR: LWGEOM_dwithin: Operation on mixed SRID geometries (Point, 0) != (Point, 2100)

I am completely new to PostGIS, but it seems like the script I have used to populate the GeoPoint columns is incorrect.

UPDATE:

I have changed my update script to

UPDATE "Properties" SET "GeoPoint" = ST_SetSRID((ST_MakePoint(CAST("Longitude" AS double precision), CAST("Latitude" AS double precision))), 23700)

and my select statement to

SELECT p."Id", p."Longitude", p."Latitude", p."GeoPoint" 
FROM "Properties" p
WHERE ST_DWithin(
        p."GeoPoint",
        ST_Transform(
            ST_GeomFromText(
                'POINT(19.081750000000 47.54770000000)',
                4326),
            23700),
        500000)

Running this select statement returns no error, but it does not return any results either. Distance is set to 500000, which should be 500 km and the table contains multiple sample record within 3 to 20 km distance. If I change the distance to 5 000 000, then it returns all rows even some located 17+ km away

UPDATE AGAIN:

I have googled it and SRID 23700 supposed to be the correct one Hungary and distance should be in meters. I still do not get it. If I run the following:

select ST_Distance(
'SRID=23700;POINT(19.067931856332 47.511673637045)'::geometry, 
'SRID=23700;POINT(19.081750000000 47.54770000000)'::geometry)

It returns 0.03858548849237025 which is clearly not in meters. What am I doing wrong?

6
  • There seems to be a projection issue. You convert yor WGS84 coordinates to EPSG:2100 which is valid for Greece: epsg.io/2100 - look for a projected CRS valid for Hungary and than first re-project your points to this. – Babel Apr 11 at 8:52
  • Thanks for the link, I have found it: epsg.io/23700 - but I still get the same error. Does these mean I have to set this id when I populate the GeoPoint columns as well? It still says operation on mixed SRID geometries – Sugafree Apr 11 at 9:17
  • Please Edit the question to contain the error message. Note that best practice is to use all lowercase column names, so you don't need to double-quote every column identifier. – Vince Apr 11 at 11:05
  • @Vince thanks for the advice ont hte column names. The error message is already in the first part of the question. The edited select statement does not return any error message, but the results are incorrect – Sugafree Apr 11 at 12:49
  • when you set geopoint, you are using lat-longs and you assign srid 23700. I seems you should assign 4326 and then do a transform to 23700. The strange results you get occurs because your points are around coordinates 0m;0m(+- 90 meters / +-180 meters) instead of degrees – JGH Apr 11 at 15:15
2

ST_Distance and ST_DWithin uses the projection metrics. If your projection is in meter it will uses meters, but if your projection is in degree it will uses degrees.

In your case, you want meters, and your projection is in meter, so it should be good. I think the problem is that your data is in degree. When you use the function ST_SetSRID it does not project your data, it just set the projection so all the function you use will recognize this field as a geometry in this projection. If you have longitude and latitude, your projection is 4326. If you want your data in 23700, you need to transform it.

So for example when you do:

select ST_Distance(
'SRID=23700;POINT(19.067931856332 47.511673637045)'::geometry, 
'SRID=23700;POINT(19.081750000000 47.54770000000)'::geometry)

You define 2 points around 19 meter in X and 47 meter in Y in the 23700. But that's clearly GPS point, so in 4326. I you want them in 23700, you need to do:

select ST_Distance(
ST_Transform('SRID=4326;POINT(19.067931856332 47.511673637045)'::geometry, 23700), 
ST_Transform('SRID=4326;POINT(19.081750000000 47.54770000000)'::geometry, 23700))

So you need to modify your update script to add the transform:

ST_Transform(ST_SetSRID((ST_MakePoint(CAST("Longitude" AS double precision), CAST("Latitude" AS double precision))), 4326), 23700)

You could also not use any projection and work directly in 4326, and use ST_Distance or ST_DWithin with your geom::geography to use them with meters. Postgis will automatically project using UTM if possible.

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.