[postgis-users] postgis-users Digest, Vol 101, Issue 15
venkat
ven.tammineni at gmail.com
Mon Sep 20 06:27:00 PDT 2010
Dear All,
When PostGIS2.0 will be out for windows.
Thanks and Regards,
Venkat
On Thu, Sep 16, 2010 at 12:30 AM, <
postgis-users-request at postgis.refractions.net> wrote:
> Send postgis-users mailing list submissions to
> postgis-users at postgis.refractions.net
>
> 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
> postgis-users-request at postgis.refractions.net
>
> You can reach the person managing the list at
> postgis-users-owner at postgis.refractions.net
>
> 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 <cspam1234 at gmail.com>
> Subject: [postgis-users] Inserting Geometries into PostGres database -
> ???
> To: postgis-users at postgis.refractions.net
> Message-ID: <C05C04F7-4F38-42D5-8C94-91D910C2E336 at gmail.com>
> 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 <woodbri at swoodbridge.com>
> Subject: Re: [postgis-users] Inserting Geometries into PostGres
> database - ???
> To: postgis-users at postgis.refractions.net
> Message-ID: <4C8FEB70.3040808 at swoodbridge.com>
> 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
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> ------------------------------
>
> Message: 3
> Date: Tue, 14 Sep 2010 19:49:25 -0700
> From: Kevin Neufeld <kneufeld at refractions.net>
> Subject: Re: [postgis-users] unioning a buffer - bug?
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <4C903435.8030802 at refractions.net>
> 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 <kneufeld at refractions.net>
> > *To:* postgis-users at postgis.refractions.net
> > *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
> >> 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
> >
> -------------- 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 <kneufeld at refractions.net>
> Subject: Re: [postgis-users] Inserting Geometries into PostGres
> database - ???
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <4C903B84.4060903 at refractions.net>
> 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
> >> 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
>
>
> ------------------------------
>
> Message: 5
> Date: Wed, 15 Sep 2010 10:45:14 +0530
> From: venkat <ven.tammineni at gmail.com>
> Subject: [postgis-users] ST_Split function is not working in PostGIS
> 1.5
> To: postgis-users at postgis.refractions.net
> Message-ID:
> <AANLkTik7Ke8JCvTzxypgxA+ggx0ujcgPrKkf6K8TQuiT at mail.gmail.com<AANLkTik7Ke8JCvTzxypgxA%2Bggx0ujcgPrKkf6K8TQuiT at mail.gmail.com>
> >
> 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" <lr at pcorp.us>
> Subject: Re: [postgis-users] ST_Split function is not working in
> PostGIS 1.5
> To: "'PostGIS Users Discussion'"
> <postgis-users at postgis.refractions.net>
> Message-ID: <ECD1AA3E450746AE8896BDACB5580076 at 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: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of venkat
> Sent: Wednesday, September 15, 2010 1:15 AM
> To: postgis-users at postgis.refractions.net
> 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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> End of postgis-users Digest, Vol 101, Issue 15
> **********************************************
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100920/48fd4c87/attachment.html>
More information about the postgis-users
mailing list