[postgis-users] postgis-users Digest, Vol 101, Issue 15
Paragon Corporation
lr at pcorp.us
Tue Sep 21 11:03:03 PDT 2010
Venkat,
We have 2.0 binaries available for windows. Keep in mind that PostGIS 2.0
is still in alpha and has not been released yet.
You can download the windows binaries from here (for PostgreSQL 9.0 and 8.4)
. We try to update them once every 2 weeks or when anything exciting
happens.
http://www.postgis.org/download/windows/experimental.php
We'll probably put up new versions later this week around the time we put up
the PostGIS 1.5.2 binaries.
Leo and 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: Monday, September 20, 2010 9:27 AM
To: postgis-users at postgis.refractions.net
Subject: Re: [postgis-users] postgis-users Digest, Vol 101, Issue 15
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
<mailto: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/20100921/f774501a/attachment.html>
More information about the postgis-users
mailing list