PostGIS ST_Intersects and ST_MakePoint

Question

I have a table called constraints_layer and columns named pk_id, dataset, name, geom. Geom is polygons. Within my Leaflet map Java I have a function that triggers on mouse double click and sends an SQL query to the PostGIS database intended to select data from the columns pk_id, dataset, name, geom within table constraints_layer where the clicked point intersects with any of the polygons (geom).

I have come up with the following query:

let sqlQueryPointIntersect = 
     "SELECT pk_id, dataset, name, geom FROM constraints_layer WHERE ST_Intersects (geom, 'SRID=4326;ST_MakePoint(" + 
        click.lng + "," + click.lat + 
        ")');";

fetch(url + sqlQueryPointIntersect)
        .then(function(response) {
            return response.json();
        })
        .then(function(data) {
            

          console.log (data)

But get the error:

“Uncaught (in promise) Error: Invalid GeoJSON object”

In an attempt to break this down I have written the following query which worked when entered along with the url into the web browsers address bar.

let sqlQueryPointIntersectTEST= 

       "SELECT pk_id, dataset, name, geom FROM constraints_layer WHERE ST_Intersects (geom, 'SRID=4326;POINT( -0.338087 51.250420 )');";

My question is why does sqlQueryPointIntersect not work?

I’ve tried removing the “,” between lat and long but no joy.

Answer 1

I think you are mixing up ST_MakePoint(lon, lat) (a SQL function call, not a string) and POINT(lon lat) WKT string syntax.

In first sample, you have

... WHERE ST_Intersects (geom, 'SRID=4326;ST_MakePoint(" + 
        click.lng + "," + click.lat + 
        ")');";

This creates a string like

SRID=4326;ST_MakePoint(-0.338087, 51.250420 )

which is not a valid WKT string.

In the query example that works you use valid WKT - POINT, and no comma:

SRID=4326;POINT( -0.338087 51.250420 ).

This string can be converted to Geometry type.

---

I would try either function calls only, no strings, like:

"... WHERE ST_Intersects (geom, ST_SetSRID(ST_MakePoint(" + click.lng + "," + click.lat + "), 4326))"

or proper WKT string:

"... WHERE ST_Intersects (geom, 'SRID=4326;Point(" + click.lng + " " + click.lat + ")');";

Note I've replaced ST_MakePoint with Point, and replaced comma with space here.