1

I am trying to insert a GeoJSON into PostGIS, but I am encountering a syntax error. I can't seem to see where the problem is - I'm sure it is an easy issue to resolve, but my inexperienced eyes can't seem to see where the problem is.

Insert statement (PHP):

$conn = pg_connect("host=localhost dbname=postgis user=$user password=$password");

$sql = "INSERT INTO carto_doodle (poly_geom) VALUES
    (ST_SetSRID
        (ST_GeomFromGeoJSON
            ({"type":"Feature",
              "properties":{},
              "geometry":{"type":"Polygon",
                          "coordinates":
                              [[[-74.54635620117188,40.773261878622634],
                                [-74.69741821289062,40.61082491956405],
                                [-74.30740356445312,40.61603737424185],
                                [-74.54635620117188,40.773261878622634]]]
                          }
              })
          ),
     4326)"

$success = pg_query($conn, $sql);

However, this returns an error for the $success line:

Warning: pg_query(): Query failed: ERROR: syntax error at or near "{" LINE 1: ...(poly_geom) VALUES (ST_SetSRID(ST_GeomFromGeoJSON({"type":"F... ^

Here's what ended up working for me...

$sql = 'INSERT INTO carto_doodle (point_geom) VALUES
       (ST_SetSRID(ST_GeomFromGeoJSON(\'
            {"type":"Point",
             "coordinates":
                          [-48.23456, 20.12345]
            }\'
        ), 4326))';
  • Is that INSERT statement run fine in SQL console ? – WKT Apr 24 '16 at 16:57
  • 1
    Also note: ST_GeomFromGeoJSON works only for JSON Geometry fragments. It throws an error if you try to use it on a whole JSON document. – nickves Apr 24 '16 at 21:21
  • Oh ok cool thanks - I have to put a little more thought into this... – skwidbreth Apr 24 '16 at 22:42
  • Yes, thanks @nickves - that was really helpful. Editing my question to show the final working syntax... – skwidbreth Apr 25 '16 at 21:26
2

Here an example with ST_GeomFromGeoJSON() :

  select ST_GeomFromGeoJSON
        ('{"type":"Polygon",
                      "coordinates":
                          [[-74.54635620117188,40.773261878622634],
                            [-74.69741821289062,40.61082491956405],
                            [-74.30740356445312,40.61603737424185],
                            [-74.54635620117188,40.773261878622634]]
          }')
  • This generates the error Parse error: syntax error, unexpected '{', referring to the curly brace right after the first single quote you added. – skwidbreth Apr 24 '16 at 17:42
  • I have edited with a running example. – WKT Apr 24 '16 at 17:56
  • Thanks man, this was really helpful. After some more experimentation, I was able to get it to work - I've edited my question to show the final syntax... – skwidbreth Apr 25 '16 at 21:25

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.