1

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.

7
  • because it isn't valid SQL? where is your ST_MakePoint?
    – Ian Turton
    Commented Jul 19, 2023 at 15:53
  • On the line : SELECT pk_id, dataset, name, geom FROM constraints_layer WHERE ST_Intersects (geom, 'SRID=4326;ST_MakePoint(" + You have to scroll across or perhaps should I have posted the code differently in the question.
    – williamh
    Commented Jul 19, 2023 at 17:33
  • But it is missing in the second SQL snippet
    – Ian Turton
    Commented Jul 19, 2023 at 17:49
  • the second SQL query was my attempt to demonstrate that the query works when a specific set of lat long coordinates are entered manually into the query. So the problem with the first query lies with the part where I’ve attempted to replace the fixed (manual) coordinates with the ST_make point lat long based on the user double click.
    – williamh
    Commented Jul 19, 2023 at 18:28
  • Could you output or console.log sqlQueryPointIntersect to make sure it's constructing the way you are expecting? Maybe the click.lng and click.lat values are formatted strange?
    – Mintx
    Commented Jul 19, 2023 at 20:52

1 Answer 1

4

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.

1
  • Your right, the proper WKT string seems to work. Seems I need to go back and properly understand the differnce betwen an SQL function call vs a string.
    – williamh
    Commented Jul 20, 2023 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.