Dear All, When PostGIS2.0 will be out for windows.
Thanks and Regards, Venkat On Thu, Sep 16, 2010 at 12:30 AM, < [email protected]> wrote: > Send postgis-users mailing list submissions to > [email protected] > > To subscribe or unsubscribe via the World Wide Web, visit > http://postgis.refractions.net/mailman/listinfo/postgis-users > or, via email, send a message with subject or body 'help' to > [email protected] > > You can reach the person managing the list at > [email protected] > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of postgis-users digest..." > > > Today's Topics: > > 1. Inserting Geometries into PostGres database - ??? (CS) > 2. Re: Inserting Geometries into PostGres database - ??? > (Stephen Woodbridge) > 3. Re: unioning a buffer - bug? (Kevin Neufeld) > 4. Re: Inserting Geometries into PostGres database - ??? > (Kevin Neufeld) > 5. ST_Split function is not working in PostGIS 1.5 (venkat) > 6. Re: ST_Split function is not working in PostGIS 1.5 > (Paragon Corporation) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Tue, 14 Sep 2010 16:12:23 -0500 > From: CS <[email protected]> > Subject: [postgis-users] Inserting Geometries into PostGres database - > ??? > To: [email protected] > Message-ID: <[email protected]> > Content-Type: text/plain; charset="us-ascii" > > 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))"; > > 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 > -------------- next part -------------- > An HTML attachment was scrubbed... > URL: < > http://postgis.refractions.net/pipermail/postgis-users/attachments/20100914/ee037f18/attachment-0001.html > > > > ------------------------------ > > Message: 2 > Date: Tue, 14 Sep 2010 17:38:56 -0400 > From: Stephen Woodbridge <[email protected]> > Subject: Re: [postgis-users] Inserting Geometries into PostGres > database - ??? > To: [email protected] > Message-ID: <[email protected]> > Content-Type: text/plain; charset=ISO-8859-1; format=flowed > > 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 > > [email protected] > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > ------------------------------ > > Message: 3 > Date: Tue, 14 Sep 2010 19:49:25 -0700 > From: Kevin Neufeld <[email protected]> > Subject: Re: [postgis-users] unioning a buffer - bug? > To: PostGIS Users Discussion <[email protected]> > Message-ID: <[email protected]> > Content-Type: text/plain; charset="iso-8859-1"; Format="flowed" > > Interesting. Something sure sounds amiss. Are the results null? Is > there anything usual occurring at the TopologyException location? Is > your dataset large? Can you winnow it down to the few polygons that > might be causing the problem (this may be just the polygons around the > TopologyException location)? If so, you could paste the representation > of the geometries in PasteBin or something so we can help you isolate > the problem. > > Also, what version of GEOS do you have installed? > SELECT PostGIS_Full_Version(); > > Cheers, > Kevin > > On 9/14/2010 6:03 AM, Lee wrote: > > Hi Kevin, > > > > I assumed I have no results because when I load one of the working > > queries as a view (eg. select st_buffer(the_geom,0.1)) in QGIS, or in > > ArcGIS, I see all my results as I would expect, but when I modify the > > query to st_union(st_buffer()), all my parcels disappear and I have a > > blank canvas. > > > > Also, with st_isvalid(st_union(st_buffer(the_geom,0.1))) returning > > null (neither t nor f) I guess I just assumed. Anyways, results for > > your suggestions are inline, below. > > > > Thanks for your help. Any further ideas? > > > > Lee. > > ------------------------------------------------------------------------ > > *From:* Kevin Neufeld <[email protected]> > > *To:* [email protected] > > *Sent:* Tue, September 14, 2010 12:12:46 AM > > *Subject:* Re: [postgis-users] unioning a buffer - bug? > > > > How are you determining you have no results? > > > > Try: > > CREATE TABLE results AS > > SELECT st_union(st_buffer(the_geom,0.1)) as the_geom > > FROM base.current_assessment_parcel; > > > > NOTICE: TopologyException: found non-noded intersection between > > 586714 4.95189e+006, 586714 4.95189e+006 and 586714 4.95189e+006, > > 586739 4.9519e+006 586714 4.95189e+006 > > Query returned successfully with no result in 1078 ms. > > > > > > > > SELECT count(*) FROM results; > > > > 1 > > > > SELECT ST_Summary(the_geom) FROM results; > > > > This returns blank. > > > > > > (snip) > > > > -- Kevin > > > > On 9/13/2010 7:07 AM, Lee wrote: > >> So after some googling I see similar problems, but no solutions posted. > >> > >> I am trying to union a buffer, but the query is returning no > >> results. Here is ultimately what I would like to achieve: > >> > >> select st_union(st_buffer(the_geom,0.1)) as the_geom from > >> base.current_assessment_parcel > >> > >> Here are some troubleshooting steps I have taken to try narrow it down: > >> > >> select st_isvalid(the_geom) a from base.current_assessment_parcel > >> group by a > >> TRUE > >> > >> select st_isvalid(st_buffer(the_geom,0.1)) a from > >> base.current_assessment_parcel group by a > >> TRUE > >> > >> select st_isvalid(st_union(the_geom)) from > base.current_assessment_parcel > >> TRUE > >> > >> select st_isvalid(st_union(st_buffer(the_geom,0.1))) from > >> base.current_assessment_parcel > >> NOTICE: TopologyException: found non-noded intersection between > >> 586714 4.95189e+006, 586714 4.95189e+006 and 586714 4.95189e+006, > >> 586739 4.9519e+006 586714 4.95189e+006 > >> Total query runtime: 859 ms. > >> 1 row retrieved. > >> (returns blank record) > >> > >> And just for fun > >> select st_isvalid(st_buffer(st_union(the_geom),0.1)) a from > >> base.current_assessment_parcel > >> TRUE > >> > >> I guess my next step would be to try the snap to grid functions, but > >> the first query above should work, shouldn't it? > >> > >> Any help appreciated. Thanks. > >> Lee > >> > >> > >> _______________________________________________ > >> postgis-users mailing list > >> [email protected] > >> http://postgis.refractions.net/mailman/listinfo/postgis-users > >> > > > > > > _______________________________________________ > > postgis-users mailing list > > [email protected] > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > -------------- next part -------------- > An HTML attachment was scrubbed... > URL: < > http://postgis.refractions.net/pipermail/postgis-users/attachments/20100914/5410d8cd/attachment-0001.html > > > > ------------------------------ > > Message: 4 > Date: Tue, 14 Sep 2010 20:20:36 -0700 > From: Kevin Neufeld <[email protected]> > Subject: Re: [postgis-users] Inserting Geometries into PostGres > database - ??? > To: PostGIS Users Discussion <[email protected]> > Message-ID: <[email protected]> > Content-Type: text/plain; charset=ISO-8859-1; format=flowed > > 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 > >> [email protected] > >> http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > _______________________________________________ > > postgis-users mailing list > > [email protected] > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > ------------------------------ > > Message: 5 > Date: Wed, 15 Sep 2010 10:45:14 +0530 > From: venkat <[email protected]> > Subject: [postgis-users] ST_Split function is not working in PostGIS > 1.5 > To: [email protected] > Message-ID: > > <[email protected]<aanlktik7ke8jcvtzxypgxa%[email protected]> > > > Content-Type: text/plain; charset="iso-8859-1" > > Dear All, > > I am trying use ST_split function.when i use that function is saying that > ST_Split function does not exit and error is below... > > ERROR: function st_split(geometry, geometry) does not exist > LINE 1: SELECT ST_Split(circle, line) > HINT: No function matches the given name and argument types. You might > need > to add explicit type casts. > > > Please can you anyone guide me.. I am waiting fro your great response. > > Thanks and Regards, > > Ven > -------------- next part -------------- > An HTML attachment was scrubbed... > URL: < > http://postgis.refractions.net/pipermail/postgis-users/attachments/20100915/30d37439/attachment-0001.html > > > > ------------------------------ > > Message: 6 > Date: Wed, 15 Sep 2010 02:23:38 -0400 > From: "Paragon Corporation" <[email protected]> > Subject: Re: [postgis-users] ST_Split function is not working in > PostGIS 1.5 > To: "'PostGIS Users Discussion'" > <[email protected]> > Message-ID: <ecd1aa3e450746ae8896bdacb5580...@j> > Content-Type: text/plain; charset="us-ascii" > > Ven, > > Sorry ST_Split function does not exist in 1.5. ST_Split is a new function > in PostGIS 2.0 > http://www.postgis.org/documentation/manual-svn/ST_Split.html > See the Availability: 2.0.0 note? > > > this is the list of new or changed functions in 2.0 > http://www.postgis.org/documentation/manual-svn/ch08.html#NewFunctions_2_0 > > Sorry, > Regina > http://www.postgis.us > > > > _____ > > From: [email protected] > [mailto:[email protected]] On Behalf Of venkat > Sent: Wednesday, September 15, 2010 1:15 AM > To: [email protected] > Subject: [postgis-users] ST_Split function is not working in PostGIS 1.5 > > > Dear All, > > > I am trying use ST_split function.when i use that function is saying that > ST_Split function does not exit and error is below... > > > ERROR: function st_split(geometry, geometry) does not exist > LINE 1: SELECT ST_Split(circle, line) > HINT: No function matches the given name and argument types. You might > need > to add explicit type casts. > > > Please can you anyone guide me.. I am waiting fro your great response. > > Thanks and Regards, > > Ven > -------------- next part -------------- > An HTML attachment was scrubbed... > URL: < > http://postgis.refractions.net/pipermail/postgis-users/attachments/20100915/e3302bc1/attachment-0001.html > > > > ------------------------------ > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > End of postgis-users Digest, Vol 101, Issue 15 > ********************************************** >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
