0

I have inserted multiple location rows with geojson value on a geometry column and trying to search it based on a point value, I need to find the point is exists on the polygon or not? I've seen this https://stackoverflow.com/a/38941705/1138192 but I have geojson value to insert and search by point.

What I have tried So far:

CREATE TABLE areas (
    id SERIAL PRIMARY KEY,
    name VARCHAR(64),
    the_geom GEOMETRY
);

CREATE INDEX areas_the_geom_idx ON areas USING GIST (the_geom);

INSERT INTO areas (name, the_geom)
    VALUES ('Corrected_Shape', 
    ST_TRANSFORM(ST_GeomFromGeoJSON('{
    "type":"Polygon",
    "coordinates":[[
        [-91.23046875,45.460130637921],
        [-79.8046875,49.837982453085],
        [-69.08203125,43.452918893555],
        [-88.2421875,32.694865977875],
        [-91.23046875,45.460130637921]
    ]],
    "crs":{"type":"name","properties":{"name":"EPSG:4326"}}
}'),3857));

SELECT name FROM areas WHERE ST_Contains(the_geom, ST_GeomFromText('POINT(43.452918 -69.082031)'));

enter image description here

A l w a y s S u n n y
  • 36,497
  • 8
  • 60
  • 103
  • What is the problem exactly? by a quick look you do not have a SRID here: ST_GeomFromText('POINT(43.452918 -69.082031) – milad Sep 30 '20 at 13:39

1 Answers1

1

First, it seems you have swapped longitude and latitude when creating the point (it should be longitude first)

--> ST_GeomFromText('POINT(-69.082031 43.452918)')

Then, you have projected the polygon geometry to 3857 but not the point. You must use the same CRS for both geometries.

--> ST_TRANSFORM(ST_GeomFromText('POINT(-69.082031 43.452918)'),3857)

JGH
  • 15,928
  • 4
  • 31
  • 48
  • Finally this one works for me `SELECT * FROM locations_ep WHERE ST_Intersects(bounding_polygon, ST_GeomFromText('SRID=4326;POINT(8.56483 47.45267)'));` – A l w a y s S u n n y Oct 01 '20 at 05:58