[postgis-users] Inserting Geometries into PostGres database - ???

[Kevin Neufeld]

Here's some sample code that illustrates how to programmatically read 
and write JTS Geometries from a PostGIS database in Java.  The writing 
is done through a PreparedStatement to set the bounds of a SELECT 
query.  It's the same concept if your PreparedStatement represents an 
INSERT statement instead.
BTW, I would not recommend you use WKT.  WKT will result in coordinate 
drifts due to loss of precision.  If you're doing things 
programmatically, use WKB.  However, if you're not concerned with 
precision loss, you can easily enough use pstmt.setString(1, 
myJTSPolygon.toString()) instead (and of course use ST_GeomFromText(?, 
<srid>) in the query).  Stephen's inline solution should also work.



// PostGIS and JTS
Class.forName("org.postgresql.Driver");
Connection conn =
    DriverManager.getConnection("jdbc:postgresql://...");

WKBReader wkbReader = new WKBReader();
WKBWriter wkbWriter = new WKBWriter();

String query =
    "SELECT the_geom FROM my_spatial_table
     WHERE the_geom && ST_GeomFromWKB(?, 3005)");
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setBytes(1, wkbWriter.write(myJTSPolygon);

ResultSet rs = pstmt.executeQuery();
while(rs.next) {
    Geometry g = wkbReader.read(WKBReader.hexToBytes(
                    rs.getString(1)));
    ...
    // Do stuff with Geometry
}


Hope this helps,
Kevin

On 9/14/2010 2:38 PM, Stephen Woodbridge wrote:
> On 9/14/2010 5:12 PM, CS wrote:
>> Hi all. I am having problems inserting a geometry type into a database.
>> I am using postgres 8.4 and whatever the latest postgis there is(i
>> forgot now). Linux with glassfish is the server environment.
>>
>> I have seen multiple hardcoded examples about how to insert something in
>> the VALUES clause but what if i have an array of polygons that cannot be
>> hardcoded that is already determined? That is the coordinates and type
>> of the shape is already decided. This lends to the question as to
>> whether you can use a preparedStatement and setString to insert a
>> geometry. This is what i need some help on.
>>
>>
>> an example of a WKT for a certain cell that i have is:
>>
>> POLYGON ((-90.406 31.07360133086052, -90.406 31.34423828125,
>> -90.09073538749632 31.34423828125, -90.09073538749632 31.07360133086052,
>> -90.406 31.07360133086052))
>>
>> i want to be able to take this(as a string, if possible) and insert into
>> the database using a prepared statement.
>>
>>
>> PreparedStatement.setString(1, cells[cellIndex].getShape().getWkt());,
>> where getWKT returns the string representation. Of course this doesnt
>> work because the type of the column is a postgis geometry. So, again,
>> how do you use a prepared statement to insert a WKT string 
>> representation?
>>
>>
>> I have tried this:
>>
>> String tempString = cells[cellIndex].getShape().getWkt();
>> String parameterInsert = "insert into Cell(result, report, modified,
>> cellid, missionid, shape) values (?, ?, ?, ?, ?,
>> GeomFromText(tempString,4326))";
>
> You might try something like:
>
> String parameterInsert = "insert into Cell(result, report, modified,
> cellid, missionid, shape) values (?, ?, ?, ?, ?,
>  GeomFromText(" + tempString + ",4326))";
>
> And see if that works.
>
> -Steve W
>
>> where tempString would be the POLYGON above and i get the error:
>> org.postgresql.util.PSQLException: ERROR: Invalid OGC WKT (does not
>>
>> start with P,L,M or G), which doesnt really make much sense to me.
>>
>>
>> I try to put single quotes around each one like:
>>
>> String tempString = "'" + cells[cellIndex].getShape().getWkt() + "'";
>>
>> and i get the error that tempString as a column does not exist.
>>
>> There also seems to be some confusion on the postgis message boards
>> whether a single quote is even needed. I was never able to come to a
>> definite conclusion.
>>
>>
>> I would appreciate help to all who could help. ive tried everything that
>> i know at this point. Thanks a bunch in advance!
>>
>> CS
>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users