Re: [postgis-users] shp2pgsql-gui and option -S (Generate simple instead of MULTI geometries) / Ticket #660?
On Mon, Jan 30, 2012 at 07:40:01AM +0100, Stefan Keller wrote: 2012/1/27 Sandro Santilli s...@keybit.net: On Thu, Jan 26, 2012 at 09:50:25PM +0100, Stefan Keller wrote: ... Am I missing something? A ticket on the trac system, most likely :) So you think it's not redundant to ticket #660? Oops, It would. Sorry! --strk; () Free GIS Flash consultant/developer /\ http://strk.keybit.net/services.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] What about the latest release 2.0?
On Mon, Jan 30, 2012 at 12:49:18AM -0800, Giannis Giakoumidakis wrote: It's ending of January, when we should expect the new release of Post GIS 2.0? You should expect it to be out as soon as all blocker bugs are closed: http://trac.osgeo.org/postgis/query?status=assignedstatus=newstatus=reopenedgroup=statusmilestone=PostGIS+2.0.0 Testing alpha3 will help catching other issues: http://www.postgis.org/download/ --strk; ,--o-. | __/ |Thank you for PostGIS-2.0 Topology ! | / 2.0 |http://www.pledgebank.com/postgistopology `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Installation of PL/R
Volkmar, Many thanks for trying to help. Unfortunately I already did all of those things. To make matters worse I just tried the procedure on my 32bit windows system in my office and I am getting the same issue. The dll is in the right place and the system variable and PATH are setup as per the instructions. I also checked that plr is not already installed. I have stopped and restarted the PG service (including complete reboots) but to no avail. Is this a problem with PG9.1? Either that or I am systematically doing something wrong :( . The download site gives me the plr-8.3.0.13 zip from a link saying Win32 plr.dll for Postgres 9.0.x but above it includes 9.1 in the section title. I will persevere as I would like to do analysis from within postgresql/postgis2 if at all possible. Best wishes Darrel From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Volkmar Herbst Sent: 30 January 2012 07:18 To: 'PostGIS Users Discussion' Subject: Re: [postgis-users] Installation of PL/R HI, I did install R several times and run also often into this issue. Try the 32bit one and set the path C:\Program Files\R\R-2.13.1\bin\i386 in your environment. Create new variable R_HOME value-C:\Program Files\R\R-2.13.1 Make sure that plr.dll is in C:\Program Files (x86)\PostgreSQL\9.0\lib. Restart postgres after that. If you receive errors check if you did not install it already by using plr_environ() in postgres. These are the few things I can think of. May be you have already tried them all.. Volkmar Von: postgis-users-boun...@postgis.refractions.netmailto:postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net]mailto:[mailto:postgis-users-boun...@postgis.refractions.net] Im Auftrag von Darrel Maddy Gesendet: Samstag, 28. Januar 2012 18:45 An: postgis-users@postgis.refractions.netmailto:postgis-users@postgis.refractions.net Betreff: [postgis-users] Installation of PL/R Apologies if this is not the right mail list for this but my confusion arises because of my 32 bit PostGIS install! I have postgis2SVN sitting on top of postgresql 9.1 (32 bit) on a 64 bit version of windows (working fine). I installed R-2.14.1 and tried to follow the instructions given here http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut01 Now I tried both the 32bit and 64bit dll from http://www.joeconway.com/plr/ I ran the sql script supplied but get ERROR: could not load library C:/Program Files (x86)/PostgreSQL/9.1/lib/plr.dll: The specified procedure could not be found. Needless to say plr.dll is in that location (I have tried both versions). Any suggestions would be welcome (and apologies if this is a dumb question). Thanks Darrel ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] PostGIS Topology Pledge: completed !
As of revision 8963 (included in upcoming 2.0.0alpha3 [1]), the function
to convert simple layers to topologically defined layers [2] is completed.
[1] 2.0.0alpha3 http://www.postgis.org/download/
[2] toTopoGeom http://trac.osgeo.org/postgis/ticket/1017
This means that building a persistent topology for your public.states.geom
layer would be as simple as this:
SELECT CreateTopology('states_topo');
SELECT AddTopoGeometryColumn('states_topo',
'public', 'states', 'topogeom',
'POLYGON');
UPDATE public.states
SET topogeom = toTopoGeom(geom, 'states_topo');
You can then check correctness of the conversion:
SELECT gid FROM public.states WHERE NOT ST_Equals(geom, topogeom);
Look for area overlaps:
SELECT r1.element_id FROM states_topo.relation r1, states_topo.relation r2
WHERE r1.topogeo_id != r2.topogeo_id AND r1.element_id = r2.element_id;
Or underlaps:
SELECT face_id FROM istat_topo.face WHERE face_id 0
AND face_id NOT IN ( SELECT element_id FROM istat_topo.relation );
Perform any editing required to clean things up [3], or to simplify the edges.
You can take a look at the primitives with QGIS db_manager plugin [4], or even
at the actual TopoGeometries with QGIS master [5] (although it will be slow in
selecting features within the viewport, see ticket #1290 [6]).
[3] http://strk.keybit.net/blog/2011/11/21/topology-cleaning-with-postgis/
[4] qgis db_manager http://www.qgis.org/wiki/DB_Manager_plugin_GSoC_2011
[5] QuantumGIS http://www.qgis.org
[6] overlap TopoGeometry http://trac.osgeo.org/postgis/ticket/1290
And of course you can convert TopoGeometries back to simple geometries when
needed
for performance or compatibility reasons:
ALTER TABLE public.states ADD newgeom geometry;
UPDATE public.states SET newgeom = topogeom::geometry;
Happy edge walking!
http://strk.keybit.net/blog/2012/01/28/a-walk-on-the-wild-side/
C L O S I N GC R E D I T S
I was able to dedicate my time to the implementation of the toTopoGeom
function thanks to the contribution of a disparate group of people and
companies putting a part of the money each to reach the bigger target:
Andrea PeriAnne Ghisla R3 GIS
Silvio Grosso GFOSS (gfoss.it) Cooperativa Alveo
AusVet Ingvild Nystuen Luca S. Percich
Richard Greenwod Andreas Neumann Oslandia
A special thank goes to Andrea Peri for his initial kick-starter contribution
which allowed me to set an affordable target for the pledge.
Also thanks to the Geographical Free and Open Source Software association
(GFOSS) for the help with reducing paperwork involved in handling all the
contributions.
--strk;
,--o-.
| __/ |Thank you for PostGIS-2.0 Topology !
| / 2.0 |http://www.pledgebank.com/postgistopology
`-o--'
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS Topology Pledge: completed !
On Mon, Jan 30, 2012 at 01:01:12PM +0100, Jose Carlos Martinez wrote: It would be really nice if some people from QGIS, gvSIG, uDIG, etc. start thinking about building a plugin for it. We've discussed it some for QGIS in Zurich, mostly storming and comparing GRASS and PostGIS topology to see if they could share some handling code. I came back home with a few topologies drafted on tissue paper and some notes about the required interface for a QGIS Provider. But didn't organize that information yet. A pre-Zurich attempt at organizing ideas about qgis-pgis topo editing is here: https://github.com/strk/qgis_pgis_topoedit/wiki At the moment it only contains a table of ISO/SQLMM editing functions and an indication of which datastores could be affected by each, but will likely be the place in which I'll add outcomes of the Zurich meeting. --strk; ,--o-. | __/ |Thank you for PostGIS-2.0 Topology ! | / 2.0 |http://www.pledgebank.com/postgistopology `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS Topology Pledge: completed !
Thanks strk,
this is great news - thank you for your efforts,
cheers
Ben
On 30/01/2012, at 7:49 PM, Sandro Santilli wrote:
As of revision 8963 (included in upcoming 2.0.0alpha3 [1]), the function
to convert simple layers to topologically defined layers [2] is completed.
[1] 2.0.0alpha3 http://www.postgis.org/download/
[2] toTopoGeom http://trac.osgeo.org/postgis/ticket/1017
This means that building a persistent topology for your public.states.geom
layer would be as simple as this:
SELECT CreateTopology('states_topo');
SELECT AddTopoGeometryColumn('states_topo',
'public', 'states', 'topogeom',
'POLYGON');
UPDATE public.states
SET topogeom = toTopoGeom(geom, 'states_topo');
You can then check correctness of the conversion:
SELECT gid FROM public.states WHERE NOT ST_Equals(geom, topogeom);
Look for area overlaps:
SELECT r1.element_id FROM states_topo.relation r1, states_topo.relation r2
WHERE r1.topogeo_id != r2.topogeo_id AND r1.element_id = r2.element_id;
Or underlaps:
SELECT face_id FROM istat_topo.face WHERE face_id 0
AND face_id NOT IN ( SELECT element_id FROM istat_topo.relation );
Perform any editing required to clean things up [3], or to simplify the edges.
You can take a look at the primitives with QGIS db_manager plugin [4], or even
at the actual TopoGeometries with QGIS master [5] (although it will be slow in
selecting features within the viewport, see ticket #1290 [6]).
[3] http://strk.keybit.net/blog/2011/11/21/topology-cleaning-with-postgis/
[4] qgis db_manager http://www.qgis.org/wiki/DB_Manager_plugin_GSoC_2011
[5] QuantumGIS http://www.qgis.org
[6] overlap TopoGeometry http://trac.osgeo.org/postgis/ticket/1290
And of course you can convert TopoGeometries back to simple geometries when
needed
for performance or compatibility reasons:
ALTER TABLE public.states ADD newgeom geometry;
UPDATE public.states SET newgeom = topogeom::geometry;
Happy edge walking!
http://strk.keybit.net/blog/2012/01/28/a-walk-on-the-wild-side/
C L O S I N GC R E D I T S
I was able to dedicate my time to the implementation of the toTopoGeom
function thanks to the contribution of a disparate group of people and
companies putting a part of the money each to reach the bigger target:
Andrea PeriAnne Ghisla R3 GIS
Silvio Grosso GFOSS (gfoss.it) Cooperativa Alveo
AusVet Ingvild Nystuen Luca S. Percich
Richard Greenwod Andreas Neumann Oslandia
A special thank goes to Andrea Peri for his initial kick-starter contribution
which allowed me to set an affordable target for the pledge.
Also thanks to the Geographical Free and Open Source Software association
(GFOSS) for the help with reducing paperwork involved in handling all the
contributions.
--strk;
,--o-.
| __/ |Thank you for PostGIS-2.0 Topology !
| / 2.0 |http://www.pledgebank.com/postgistopology
`-o--'
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
Ben Madin
t : +61 8 6102 5535
m : +61 448 887 220
e : b...@ausvet.com.au
AusVet Animal Health Services
P.O. Box 5467
Broome WA 6725
Australia
AusVet's website: http://www.ausvet.com.au/
This transmission is for the intended addressee only and anyone else subscribed
to this mailing list. Clearly then it is not confidential information. If you
have received this transmission in error, sorry. The contents of this email are
the opinion of the writer only and are not endorsed by AusVet Animal Health
Services unless expressly stated otherwise. Although AusVet uses virus scanning
software we do not accept liability for viruses or similar in any attachments.
Congratulations on reading this boring and probably completely unnecessary bit
- you may be the only person ever to do so!
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Select smoothed lines
Hi all, my database contains a lot of paths of storms. Each storm path is generated by joining the centroids of the storm at any moment of measurement. The result is sometimes a line with a lot of edge. Can you suggest me a method to select these lines a little bit smoothed. TIA Rocco ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Select smoothed lines
On Mon, Jan 30, 2012 at 02:50:46PM +0100, Rocco Pispico wrote: Hi all, my database contains a lot of paths of storms. Each storm path is generated by joining the centroids of the storm at any moment of measurement. The result is sometimes a line with a lot of edge. Can you suggest me a method to select these lines a little bit smoothed. ST_Simplify(geometry, tolerated_drift_from_original) --strk; ,--o-. | __/ |Thank you for PostGIS-2.0 Topology ! | / 2.0 |http://www.pledgebank.com/postgistopology `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Select smoothed lines
Maybe Paul Ramsey's recent posting provides some inspiration: http://blog.opengeo.org/2012/01/27/getting-curvey/ Regards, Edward Date: Mon, 30 Jan 2012 14:50:46 +0100 From: r.pisp...@gmail.com To: postgis-users@postgis.refractions.net Subject: [postgis-users] Select smoothed lines Hi all, my database contains a lot of paths of storms. Each storm path is generated by joining the centroids of the storm at any moment of measurement. The result is sometimes a line with a lot of edge.Can you suggest me a method to select these lines a little bit smoothed. TIA Rocco ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] tool to load dbf into postgresql table
It's been around for years. Since 1.4 I'd guess. When you type shp2pgsql you should see this option: -n Only import DBF file. The OP was how to load a dbf into postgres, you're exporting with pgsql2shp, which will export only the dbf if there is not geometry column in the table being exported. Rich On Sun, Jan 29, 2012 at 9:39 PM, pcr...@pcreso.com wrote: When did this become available? I'm using pgsql2shp from Postgis version 1.5.3 for Linux, which is pretty current, and that is not a listed command line option. Cheers, Brent Wood --- On *Mon, 1/30/12, Richard Greenwood richard.greenw...@gmail.com*wrote: From: Richard Greenwood richard.greenw...@gmail.com Subject: Re: [postgis-users] tool to load dbf into postgresql table To: PostGIS Users Discussion postgis-users@postgis.refractions.net Date: Monday, January 30, 2012, 3:55 PM On Sun, Jan 29, 2012 at 7:17 PM, John Morgan jdmor...@unca.eduhttp://mc/compose?to=jdmor...@unca.edu wrote: Hello, This may be more of a postgresql question, but it is related to my postgis setup. I was hoping that someone might know of an easy way to load a dbf into postgresql table. I have used the gdal tool shp2pgsql to load a shapefile in this way and it worked great. Is there a similar tool for loading just a dbf? Thanks, Derek Derek, Use the -n switch with shp2pgsql will do what you want: shp2pgsql -n Also ogr2ogr will do the trick. ogr2ogr can also handle conversions between numerous other spatial and non-spatial data formats. ODBC is especially handy. Rich -- Richard Greenwood richard.greenw...@gmail.comhttp://mc/compose?to=richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.nethttp://mc/compose?to=postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS Topology Pledge: completed !
Thanks strk,
I was trying to use the topology functions.
UPDATE public.sa_provinces
SET topogeom = toTopoGeom(the_geom, 'sa_provinces_topo');
I get the following message:
ERROR: function totopogeom(geometry, unknown) does not exist
LINE 2: SET topogeom = toTopoGeom(the_geom, 'sa_provinces_topo');
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
Before running this function i used SET serch_path TO topology, public;
Any pointer what might i be doing wrong.
On 30 January 2012 14:47, Ben Madin li...@remoteinformation.com.au wrote:
Thanks strk,
this is great news - thank you for your efforts,
cheers
Ben
On 30/01/2012, at 7:49 PM, Sandro Santilli wrote:
As of revision 8963 (included in upcoming 2.0.0alpha3 [1]), the function
to convert simple layers to topologically defined layers [2] is
completed.
[1] 2.0.0alpha3 http://www.postgis.org/download/
[2] toTopoGeom http://trac.osgeo.org/postgis/ticket/1017
This means that building a persistent topology for your
public.states.geom
layer would be as simple as this:
SELECT CreateTopology('states_topo');
SELECT AddTopoGeometryColumn('states_topo',
'public', 'states', 'topogeom',
'POLYGON');
UPDATE public.states
SET topogeom = toTopoGeom(geom, 'states_topo');
You can then check correctness of the conversion:
SELECT gid FROM public.states WHERE NOT ST_Equals(geom, topogeom);
Look for area overlaps:
SELECT r1.element_id FROM states_topo.relation r1, states_topo.relation
r2
WHERE r1.topogeo_id != r2.topogeo_id AND r1.element_id = r2.element_id;
Or underlaps:
SELECT face_id FROM istat_topo.face WHERE face_id 0
AND face_id NOT IN ( SELECT element_id FROM istat_topo.relation );
Perform any editing required to clean things up [3], or to simplify the
edges.
You can take a look at the primitives with QGIS db_manager plugin [4],
or even
at the actual TopoGeometries with QGIS master [5] (although it will be
slow in
selecting features within the viewport, see ticket #1290 [6]).
[3]
http://strk.keybit.net/blog/2011/11/21/topology-cleaning-with-postgis/
[4] qgis db_manager http://www.qgis.org/wiki/DB_Manager_plugin_GSoC_2011
[5] QuantumGIS http://www.qgis.org
[6] overlap TopoGeometry http://trac.osgeo.org/postgis/ticket/1290
And of course you can convert TopoGeometries back to simple geometries
when needed
for performance or compatibility reasons:
ALTER TABLE public.states ADD newgeom geometry;
UPDATE public.states SET newgeom = topogeom::geometry;
Happy edge walking!
http://strk.keybit.net/blog/2012/01/28/a-walk-on-the-wild-side/
C L O S I N GC R E D I T S
I was able to dedicate my time to the implementation of the toTopoGeom
function thanks to the contribution of a disparate group of people and
companies putting a part of the money each to reach the bigger target:
Andrea PeriAnne Ghisla R3 GIS
Silvio Grosso GFOSS (gfoss.it) Cooperativa Alveo
AusVet Ingvild Nystuen Luca S. Percich
Richard Greenwod Andreas Neumann Oslandia
A special thank goes to Andrea Peri for his initial kick-starter
contribution
which allowed me to set an affordable target for the pledge.
Also thanks to the Geographical Free and Open Source Software association
(GFOSS) for the help with reducing paperwork involved in handling all the
contributions.
--strk;
,--o-.
| __/ |Thank you for PostGIS-2.0 Topology !
| / 2.0 |http://www.pledgebank.com/postgistopology
`-o--'
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
Ben Madin
t : +61 8 6102 5535
m : +61 448 887 220
e : b...@ausvet.com.au
AusVet Animal Health Services
P.O. Box 5467
Broome WA 6725
Australia
AusVet's website: http://www.ausvet.com.au/
This transmission is for the intended addressee only and anyone else
subscribed to this mailing list. Clearly then it is not confidential
information. If you have received this transmission in error, sorry. The
contents of this email are the opinion of the writer only and are not
endorsed by AusVet Animal Health Services unless expressly stated
otherwise. Although AusVet uses virus scanning software we do not accept
liability for viruses or similar in any attachments. Congratulations on
reading this boring and probably completely unnecessary bit - you may be
the only person ever to do so!
___
postgis-users mailing list
postgis-users@postgis.refractions.net
Re: [postgis-users] PostGIS Topology Pledge: completed !
On Mon, Jan 30, 2012 at 04:34:44PM +0200, Sindile Bidla wrote:
Thanks strk,
I was trying to use the topology functions.
UPDATE public.sa_provinces
SET topogeom = toTopoGeom(the_geom, 'sa_provinces_topo');
I get the following message:
ERROR: function totopogeom(geometry, unknown) does not exist
LINE 2: SET topogeom = toTopoGeom(the_geom, 'sa_provinces_topo');
Oops, my fault, it lacks a layer_id, which is the one returned by
the previous call to AddTopoGeometryColumn, so assuming you followed
my steps (and thus created a single topology layer) it becomes:
UPDATE public.states
SET topogeom = toTopoGeom(geom, 'states_topo', 1);
Full session again:
SELECT CreateTopology('states_topo');
SELECT AddTopoGeometryColumn('states_topo',
'public', 'states', 'topogeom',
'POLYGON');
UPDATE public.states
SET topogeom = toTopoGeom(geom, 'states_topo', 1);
--strk;
,--o-.
| __/ |Thank you for PostGIS-2.0 Topology !
| / 2.0 |http://www.pledgebank.com/postgistopology
`-o--'
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Recursive intersect
On Thu, Jan 26, 2012 at 9:33 PM, Nicolas Ribot nicolas.ri...@gmail.comwrote: From: Nicolas Ribot nicolas.ri...@gmail.com Hi WITH RECURSIVE needs an UNION ALL to link the non recursive term with the recursive one. Wow, that at least runs... though I think the recursion might be infinite. Thanks for the tip Nicolas. Hi, Concerning the infinite iteration, one trick could be to use a boolean value to test if some condition is reached (for instance, no more polygon is found). Using an array to accumulate some values and test the current value against the array may be very efficient to control the iteration. Here is an extract of code that uses array to store already treated values and compare this list with the current id: (the purpose of this query was to find buildings by proximity search from a given building) with recursive mon_select as ( select -1 as id, ref, array[-1] as ids, 1 as depth, geometry from table_ori where ref = 1 UNION ALL select distinct on(s.gid) s.gid as sel_gid, m.ref, m.ids || s.gid, m.depth+1, s.geometry from table_selection s, mon_select m where st_touches(m.geometry, s.geometry) and not (s.gid = any(ids)) ) select distinct on (id) id, ref, ids, depth, geometry from mon_select; The key parts are: • The array[-1] in the non-recursive term, to initiate the array • the m.ids || s.gid array concatenation in the select, to fill up the array of ids • the not (s.gid = any(ids)) in the where clause, returning true if any value in the arrays (ids) meets the s.gid = ... condition. Thank you very much for your help with this, I'll give it a try. Leslie ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] PostGIS Topology Pledge: completed !
Excellent work strk, thanks for this! Could you please provide some examples how this can be used in queries? Does it speed up querying for neighbours etc.? Also, does it support network topologies, something I could use with pgrouting or so? Thanks, Martin -- Martin Tomko, PhD. Senior Project Manager, Information Infrastructure Design, AURIN Level 5, Architecture Building University of Melbourne VIC 3010 AUSTRALIA T: +61 3 9035 3298 E: tom...@unimelb.edu.au mailto:tom...@unimelb.edu.au W: www.aurin.org.au www.aurin.org.au W: http://martintomko.wordpress.com/ ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] nested PostGIS query closes server connection
Dear group, I've been having trouble trying to do a nested PostGIS query on our system. The query looks something like this: SELECT count, geometry, ST_GeometryType(geometry) FROM ( SELECT COUNT(*) AS count, ST_Buffer(ST_Buffer(ST_Union(ST_Buffer(ST_SimplifyPreserveTopology(geometry, 0.001), 1e-005)),.001), -.001) AS geometry FROM table WHERE criteria ORDER BY ST_GeoHash(geometry, 8), other_fields LIMIT limit OFFSET offset ) ) AS i ; This query has worked well for us numerous times, but is not working with this particular set of geometries. We have our limit set to 50. The types of errors we receive look like one of these: server closed the connection unexpectedly The connection to the server was lost. Attempting reset: Failed. I have been able to run this query successfully by paring down my limit to 1. As I mentioned above, it has worked well for us with sizes as large as 50. We are running PostGIS 1.5.3 on Postgers 9.0.4 on 64-bit Linux. Any ideas what might be happening here? *Yusuf Siddiqui * *ASPRS CMS #R149-RS Senior Solutions Engineer i-cubed : information integration imaging LLC *1600 Prospect Park Way Ft. Collins, CO 80525 Email: ysiddi...@i3.com Voice: +1-970-482-4400 Fax: +1-970-482-4499 Web: www.i3.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] tool to load dbf into postgresql table
Thanks for the feedback guys. The -n option on shp2pgsql worked well. Derek On Sun, Jan 29, 2012 at 9:17 PM, John Morgan jdmor...@unca.edu wrote: Hello, This may be more of a postgresql question, but it is related to my postgis setup. I was hoping that someone might know of an easy way to load a dbf into postgresql table. I have used the gdal tool shp2pgsql to load a shapefile in this way and it worked great. Is there a similar tool for loading just a dbf? Thanks, Derek ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] tool to load dbf into postgresql table
On Jan 29, 2012, at 11:39 PM, pcr...@pcreso.com wrote: When did this become available? I'm using pgsql2shp from Postgis version 1.5.3 for Linux, which is pretty current, and that is not a listed command line option. Not really sure when but see http://trac.osgeo.org/postgis/browser/trunk/loader/shp2pgsql-cli.c#L22 --- On Mon, 1/30/12, Richard Greenwood richard.greenw...@gmail.com wrote: On Sun, Jan 29, 2012 at 7:17 PM, John Morgan jdmor...@unca.edu wrote: Hello, This may be more of a postgresql question, but it is related to my postgis setup. I was hoping that someone might know of an easy way to load a dbf into postgresql table. I have used the gdal tool shp2pgsql to load a shapefile in this way and it worked great. Is there a similar tool for Use the -n switch with shp2pgsql will do what you want: shp2pgsql -n Also ogr2ogr will do the trick. ogr2ogr can also handle conversions between numerous other spatial and non-spatial data formats. ODBC is especially handy. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] One-to-many join
Hello, I am attempting to get a one (polys) to many (table) to load postgis data within a mapserver wms. I have the following defined in the .map file. DATA the_geom FROM (SELECT polys.gid AS gid, polys.the_g eom AS the_geom, table.pt_id AS pt_id, table.agent AS agent FROM polys RIGHT OUTER JOIN table ON polys.pt_id = table.pt_id) as new_table USING UNIQUE gid USING SRID=4326 It does load, the layer, however, it doesn't seem to be performing the one-to-many for the attributes on identify. Thanks for any feedback. Cheers, Derek ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] One-to-many join
On 01/30/2012 09:00 PM, John Morgan wrote: Hello, I am attempting to get a one (polys) to many (table) to load postgis data within a mapserver wms. I have the following defined in the .map file. DATA the_geom FROM (SELECT polys.gid AS gid, polys.the_g eom AS the_geom, table.pt_id AS pt_id, table.agent AS agent FROM polys RIGHT OUTER JOIN table ON polys.pt_id = table.pt_id) as new_table USING UNIQUE gid USING SRID=4326 It does load, the layer, however, it doesn't seem to be performing the one-to-many for the attributes on identify. Thanks for any feedback. Cheers, Derek But that would return the exact same the_geom multiple times. Which would draw all on top of each other (including the label?), so it would only look like one. What is it you are trying to do? Looks like get all the agent's to display for one area? If you are using PG 9, how about something like: DATA the_geom FROM (SELECT polys.gid AS gid, polys.the_geom AS the_geom, (select string_agg(agent, E'\n') from table where polys.pt_id = table.pt_id) AS agents FROM polys) as new_table USING UNIQUE gid USING SRID=4326 That'll return all the agents in a single string separated by carage return. -Andy ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] One-to-many join
John, I'm no expert at this, but assuming that your query returns multiple rows, I suspect this is to do with the WMS layer only identifying one feature (which then only returns one of row). This would seem like standard WMS behaviour. If you have multiple features at the same point, you might need to use WFS. If the query returns multiple rows, you are probably better to ask this on the MapServer list. There may be better ways to do this, but you may need to find the location and provide the multiple rows of data through a secondary query into another window or layer in the window. What mechanism are you using for displaying the maps and identifying - ie qgis, web template, openlayers?? cheers Ben On 31/01/2012, at 11:00 AM, John Morgan wrote: Hello, I am attempting to get a one (polys) to many (table) to load postgis data within a mapserver wms. I have the following defined in the .map file. DATA the_geom FROM (SELECT polys.gid AS gid, polys.the_g eom AS the_geom, table.pt_id AS pt_id, table.agent AS agent FROM polys RIGHT OUTER JOIN table ON polys.pt_id = table.pt_id) as new_table USING UNIQUE gid USING SRID=4326 It does load, the layer, however, it doesn't seem to be performing the one-to-many for the attributes on identify. Thanks for any feedback. Cheers, Derek ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS Topology Pledge: completed !
On Mon, Jan 30, 2012 at 10:55:41PM +, Martin Tomko wrote: Excellent work strk, thanks for this! Could you please provide some examples how this can be used in queries? Does it speed up querying for neighbours etc.? The focus insofar is about normalized storage. Speed you can squeeze out but you're mostly on your own in this phase. If you understand the model correctly you'll figure out how to build your queries in order to go faster, and what you'd like to have in the core to speed it up further. For nearest neighbours you'd use the same calls you would for simple geometries but you'd be looking at edges rather than full polygons, thus making a better use of the index. Also, does it support network topologies, something I could use with pgrouting or so? Nope, this is strictly planar topology. --strk; ,--o-. | __/ |Thank you for PostGIS-2.0 Topology ! | / 2.0 |http://www.pledgebank.com/postgistopology `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] nested PostGIS query closes server connection
On Mon, Jan 30, 2012 at 04:34:35PM -0700, Yusuf Siddiqui wrote: SELECT count, geometry, ST_GeometryType(geometry) FROM ( SELECT COUNT(*) AS count, ST_Buffer(ST_Buffer(ST_Union(ST_Buffer(ST_SimplifyPreserveTopology(geometry, 0.001), 1e-005)),.001), -.001) AS geometry FROM table WHERE criteria ORDER BY ST_GeoHash(geometry, 8), other_fields LIMIT limit OFFSET offset ) ) AS i ; ... server closed the connection unexpectedly The connection to the server was lost. Attempting reset: Failed. Can you make your input data available somewhere for us to look at ? Also, try simplifying the query to use less functions. And keep an eye on memory usage. Best if you report everything in a ticket: http://trac.osgeo.org/postgis/newticket Thanks! --strk; ,--o-. | __/ |Thank you for PostGIS-2.0 Topology ! | / 2.0 |http://www.pledgebank.com/postgistopology `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
