3

The following query and subquery help me finding a parcell using a GPS coordinate:

SELECT row_to_json(fc) FROM(
    SELECT 'FeatureCollection' As type,
    array_to_json(array_agg(f)) As features
    FROM (SELECT 'Feature' As type,
          ST_AsGeoJSON(lg.the_geom)::json As geometry,
          json_build_object('idu', idu, 'numero', numero, 'code_arr', code_arr, 'code_com', code_com, 'code_dep', code_dep, 'com_abs', com_abs, 'feuille', feuille, 'id', id, 'nom_com', nom_com, 'section', section) As properties
    FROM public.parcelle As lg
    WHERE ST_Intersects(
        lg.the_geom,
        ST_GeomFromText('POINT(4.4627171 48.505718)',4326)
        )
    ) As f)
As fc

I would like to use the properties named as lg to query the same table and get every rows that match those properties.

EDIT: Here is what I've done so far and it works, except the fact that I don't get a result every time, even if the GPS coordinates are correct and the query should actually find a result:

SELECT row_to_json(fc) FROM(
    SELECT 'FeatureCollection' As type,
    array_to_json(array_agg(f)) As features
    FROM (SELECT 'Feature' As type,
          ST_AsGeoJSON(lg.the_geom)::json As geometry,
          json_build_object('idu', pr.idu, 'numero', pr.numero, 'code_arr', pr.code_arr, 'code_com', pr.code_com, 'code_dep', pr.code_dep, 'com_abs', pr.com_abs, 'feuille', pr.feuille, 'id', pr.id, 'nom_com', pr.nom_com, 'section', pr.section) As properties
    FROM public.parcelle As lg
    LEFT JOIN public.parcelle AS pr ON ST_Intersects(
        lg.the_geom,
        ST_GeomFromText('POINT(4.4627171 48.505718)',4326)
        )
    WHERE pr.code_com = lg.code_com AND pr.code_dep = lg.code_dep AND pr.feuille = lg.feuille AND pr.section = lg.section AND pr.code_arr = lg.code_arr
    ) As f)
As fc

The features are set to null if I use the following coordinates lng:4.4627171 lat:48.505718. I've checked twice, there should be a result, there is a parcel at that position.

{
    "type": "FeatureCollection",
    "features": null
}

But when I'm using something different, a place in Paris for example, I actually get a result (lng:2.3522219 lat:48.856614):

{
    "type": "FeatureCollection",
    "features": [
        {
            "type": "Feature",
            "geometry": {
                "type": "MultiPolygon",
                "coordinates": [
                    [
                        [
                            [
                                2.35026891781536,
                                48.856341224567
                            ],
                            [
                                2.34962264291901,
                                48.8565904314831
                            ],
                            ...

Am I right using ST_Intersects here?

It seems like it can't always find a result, even if the parameters are correct and the related point should be found inside of an existing parcel.

  • 1
    What is the SRS of the_geom in parcelle? The question would be easier to answer if you focused on just the query that is causing an issue: SELECT ST_Intersects(lg.the_geom, ST_GeomFromText('POINT(4.4627171,8.505718)',4326)); which will return false if, for example, your SRS is different. – vinh Sep 12 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.