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.