0

I am new to using PostGIS and CartoDB, and I am doing some test SQL inserts into both using PHP. I've had no problem with points, but polygons are proving to be a bit tricky - my SQL insert works just fine with PostGIS on my local server (or so it seems to me), but it does not work with CartoDB.

Here's my PHP...

<?php
    $cartodb_username = "***";
    $api_key = "***";

    $cartoDBsql = "INSERT INTO polygon_test (poly_geom) VALUES (ST_GeomFromText('POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239,-71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))', 4326))";

    $ch = curl_init( "https://".$cartodb_username.".cartodb.com/api/v2/sql" );
    $query = http_build_query(array('q'=>$cartoDBsql,'api_key'=>$api_key));
    curl_setopt($ch, CURLOPT_POST, TRUE);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $query);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
    $result_not_parsed = curl_exec($ch);
?>

This code does insert points correctly, though, for example using:

$cartoDBsql = "INSERT INTO geometry_test ( the_geom ) VALUES (ST_GeomFromText('POINT($longitude $latitude)', 4326))";

... but polygons do not work. I imagine that I am not formatting the data in my insert statement correctly, but I am not sure what I am doing wrong. I've searched the CartoDB documentation and can't find an example of an ST_GeomFromText insert for a polygon.

2

ST_GeomFromText takes a WKT parameter, which is a text string of WKT. However, you can't use database parameters to format text within an SQL statement. (You would need to use PHP's text formatting utilities, like sprintf, but I don't think this is a good approach).

A better way is to use PostGIS geometry constructors that take numeric parameters, e.g.

$cartoDBsql = "INSERT INTO geometry_test (the_geom) VALUES
               (ST_SetSRID(ST_MakePoint($longitude, $latitude), 4326))";
  • 1
    Thanks buddy! This hipped me to ST_MakePolygon, which I was able to follow to this resource: e-education.psu.edu/spatialdb/node/1970 which had the syntax I needed - about halfway down the page. Dig the Zoidberg! – skwidbreth Apr 20 '16 at 1:36

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.