Re: [postgis-users] Size of .dbf file produced with pgsql2shp
Mark Cave-Ayland wrote: Ah wait a second, you're right - I've been looking in the wrong place. It looks like there is still some (old) pgsql2shp code which hasn't been updated to use the new error handling, and hence it doesn't get propagated back up to the main function correctly :( Will fix later, as I have to head out now. Okay - I've just committed a better fix as r5892 which makes sure that in the case of a 0 row result, pgsql2shp will terminate returning an error code of 1 (failure) as opposed to 0 (success) when dumping tables into shapefiles (serves me right for trying to fix things in a rush when I'm just about to head out). Denis, please can you try again after an "svn up"? ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Input versus output geometry
Recently I used pg_dump to export the output of a table with a geometry column representing a bounding box. There's an exclusion constraint on that table such that rows with overlapping bounding boxes that contained two other overlapping columns would be rejected. As it turned out I could insert the data, export it, but *not* play it back into another instance of postgres as the exclusion constraint barfed. A bit of back and forth with some of the postgres guys revealed that the output does not recreate the exact input. Is this the intended behaviour? If so, is there any way to extract a more precise representation of the geometry column? This was with Postgres 9.0rc1 and Postgis 1.5.2 SVN r5851. gisttest2=# select ST_expand(setsrid(makepoint(-122.50367,37.74189),4326), 0.4); st_expand 010320E61001000500C32ADEC83CA05EC0D044D8F0F4DE4240C32ADEC83CA05EC0ECF5EE8FF7DE424035D252793BA05EC0ECF5EE8FF7DE424035D252793BA05EC0D044D8F0F4DE4240C32ADEC83CA05EC0D044D8F0F4DE4240 (1 row) gisttest2=# select ST_expand(setsrid(makepoint(-122.50376,37.74185),4326), 0.4); st_expand 010320E6100100050063EE5A423EA05EC042EC4CA1F3DE424063EE5A423EA05EC05E9D6340F6DE4240D595CFF23CA05EC05E9D6340F6DE4240D595CFF23CA05EC042EC4CA1F3DE424063EE5A423EA05EC042EC4CA1F3DE4240 (1 row) gisttest2=# select ST_expand(setsrid(makepoint(-122.50367,37.74189),4326), 0.4) && ST_expand(setsrid(makepoint(-122.50376,37.74185),4326), 0.4); ?column? -- f (1 row) gisttest2=# select '010320E61001000500C32ADEC83CA05EC0D044D8F0F4DE4240C32ADEC83CA05EC0ECF5EE8FF7DE424035D252793BA05EC0ECF5EE8FF7DE424035D252793BA05EC0D044D8F0F4DE4240C32ADEC83CA05EC0D044D8F0F4DE4240'::geometry && gisttest2-# '010320E6100100050063EE5A423EA05EC042EC4CA1F3DE424063EE5A423EA05EC05E9D6340F6DE4240D595CFF23CA05EC05E9D6340F6DE4240D595CFF23CA05EC042EC4CA1F3DE424063EE5A423EA05EC042EC4CA1F3DE4240'::geometry; ?column? -- t (1 row) - alex ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] ST_Difference and ST_Buffer queries
Hi,
I'm not sure to understand your question.
But I think this query is quite good to clip all the polygons geometries of
a table with a single geometry.
SELECT
...
CASE
WHEN
ST_GeometryType("geom")='ST_GeometryCollection'
THEN
ST_Multi(ST_CollectionExtract("geom", 3))
ELSE
ST_Multi("geom")
END AS "geom"
FROM (
SELECT
...
ST_Intersection("geom", ST_GeomFromEWKB(decode('...', 'hex'))) AS
"geom"
FROM
"public"."table_to_clip"
WHERE
ST_Intersects("geom", ST_GeomFromEWKB(decode('...',
'hex')))=true
) AS t__0
WHERE
ST_GeometryType("geom") IN ('ST_Polygon', 'ST_MultiPolygon',
'ST_GeometryCollection')
--
-
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Size of .dbf file produced with pgsql2shp
strk wrote: I can't see how this was a success in the reporter's case. Could not determine a geometry type, so what could it have written in the .shp file ? It even printed an ERROR message. How's that a success ? --strk; Ah wait a second, you're right - I've been looking in the wrong place. It looks like there is still some (old) pgsql2shp code which hasn't been updated to use the new error handling, and hence it doesn't get propagated back up to the main function correctly :( Will fix later, as I have to head out now. ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Size of .dbf file produced with pgsql2shp
On Wed, Sep 01, 2010 at 06:29:56PM +0100, Mark Cave-Ayland wrote: > strk wrote: > > >>Checking the resulting file size is not the right way to be trying to > >>detect whether the query returned > 0 rows. It strikes me that the best > >>way to detect this would be to return a different exit code from > >>pgsql2shp if no rows are returned - would this work for you? > > > >I'd go stricter to "if an ERROR occurred" rather than > >"if no rows are returned". > > > >Just in case one day we'll add a flag to specify the geometry type > >thus allowing creation of empty-but-structuted shapefiles starting > >from a table "template". > > > >--strk; > > This is exactly how the new code in trunk currently works - you get an > exit code of 0 to indicate success, and an exit code of 1 if an error > occurred. I was thinking of adding a new exit code of 2 to indicate > success, but also that 0 records were processed. I can't see how this was a success in the reporter's case. Could not determine a geometry type, so what could it have written in the .shp file ? It even printed an ERROR message. How's that a success ? --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] Index on the Geometry Column and GEOMETRYCOLLECTION EMPTY
Sorin Dudui wrote: Hello Mark, I am wondering if you had time to work on issue. Could you reproduce it? Did the set of data work for you? Best regards, Sorin Hi Sorin, Apologies, but I just haven't had a chance to look at this yet. There's a lot of customer project work going on at the moment... ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Size of .dbf file produced with pgsql2shp
Denis Rykov wrote: It would be great if you can add an exit code for 0 records dbfs, it'll make my life so much easier. Okay done - it was quite a simple patch in the end. Can you update to SVN r5888 and confirm that you get an exit code of 0 on success, and 2 if 0 records were processed? ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Size of .dbf file produced with pgsql2shp
strk wrote: Checking the resulting file size is not the right way to be trying to detect whether the query returned > 0 rows. It strikes me that the best way to detect this would be to return a different exit code from pgsql2shp if no rows are returned - would this work for you? I'd go stricter to "if an ERROR occurred" rather than "if no rows are returned". Just in case one day we'll add a flag to specify the geometry type thus allowing creation of empty-but-structuted shapefiles starting from a table "template". --strk; This is exactly how the new code in trunk currently works - you get an exit code of 0 to indicate success, and an exit code of 1 if an error occurred. I was thinking of adding a new exit code of 2 to indicate success, but also that 0 records were processed. ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Size of .dbf file produced with pgsql2shp
It would be great if you can add an exit code for 0 records dbfs, it'll make my life so much easier. 2010/9/1 Mark Cave-Ayland : > Denis Rykov wrote: > >> Just updated pgsql2shp from svn and try to use it: pgsql2shp -f >> arkhan.shp osm "SELECT st_intersection as geom from arkhan_line_attr >> WHERE tourism<>''" >> >> If SELECT returns empty table I get notice: >> ERROR: Could not determine geometry type (empty table). >> Done (postgis major version: 2). >> Output shape: Null Shape >> Dumping: [0 rows]. >> >> At result pgsql2shp produce one .dbf file with 256 Bytes size. Some >> days ago, before updating pgsql2shp, size of such .dbf file was 1 >> Byte. >> I use size of this empty *.dbf file in my script. Need I change this >> value to 226 or this is a bug? > > Checking the resulting file size is not the right way to be trying to detect > whether the query returned > 0 rows. It strikes me that the best way to > detect this would be to return a different exit code from pgsql2shp if no > rows are returned - would this work for you? > > > ATB, > > Mark. > > -- > Mark Cave-Ayland - Senior Technical Architect > PostgreSQL - PostGIS > Sirius Corporation plc - control through freedom > http://www.siriusit.co.uk > t: +44 870 608 0063 > > Sirius Labs: http://www.siriusit.co.uk/labs > ___ > 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] Size of .dbf file produced with pgsql2shp
On Wed, Sep 01, 2010 at 12:03:36PM +0100, Mark Cave-Ayland wrote: > Denis Rykov wrote: > > >ERROR: Could not determine geometry type (empty table). > >Done (postgis major version: 2). > >Output shape: Null Shape > >Dumping: [0 rows]. > > Checking the resulting file size is not the right way to be trying to > detect whether the query returned > 0 rows. It strikes me that the best > way to detect this would be to return a different exit code from > pgsql2shp if no rows are returned - would this work for you? I'd go stricter to "if an ERROR occurred" rather than "if no rows are returned". Just in case one day we'll add a flag to specify the geometry type thus allowing creation of empty-but-structuted shapefiles starting from a table "template". --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] Size of .dbf file produced with pgsql2shp
Denis Rykov wrote: Just updated pgsql2shp from svn and try to use it: pgsql2shp -f arkhan.shp osm "SELECT st_intersection as geom from arkhan_line_attr WHERE tourism<>''" If SELECT returns empty table I get notice: ERROR: Could not determine geometry type (empty table). Done (postgis major version: 2). Output shape: Null Shape Dumping: [0 rows]. At result pgsql2shp produce one .dbf file with 256 Bytes size. Some days ago, before updating pgsql2shp, size of such .dbf file was 1 Byte. I use size of this empty *.dbf file in my script. Need I change this value to 226 or this is a bug? Checking the resulting file size is not the right way to be trying to detect whether the query returned > 0 rows. It strikes me that the best way to detect this would be to return a different exit code from pgsql2shp if no rows are returned - would this work for you? ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ERROR: You need GEOS-3.3.0 or up for ST_MakeValid
On Wed, Sep 01, 2010 at 08:21:30AM +0700, Denis Rykov wrote: > Hi, just updated GEOS to 3.3.0, but when try to use function > ST_MakeValid I get an error "ERROR: You need GEOS-3.3.0 or up for > ST_MakeValid". > SELECT postgis_full_version() returns "POSTGIS="2.0.0SVN" > GEOS="3.3.0-CAPI-1.7.0" PROJ="Rel. 4.7.1, 23 September 2009" > LIBXML="2.7.6" USE_STATS" You need to configure, build and install postgis library again for that function to work. --strk; ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Difference and ST_Buffer queries
On Tue, Aug 31, 2010 at 07:31:23PM -0400, L Bogert-O'Brien wrote: > Does anyone have an alternate suggestion that would get rid of these > unwanted borders, that would not involve changing the size of the > polygon used for clipping? What you could do is drop all components of your geometry where the area is below a given value (including holes, I'd say). It'd be useful to have the above functionality in a function, possibly taking the threshold as a factor of total area. --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
