Re: [postgis-users] topology example
On Fri, Feb 24, 2012 at 07:34:54AM +0100, Denis Rouzaud wrote: > Hi Sandro, > > Thanks again. > > I have a fresh installed for 2 days now. > Postgis 2.0 a6 (it is a 7 now) > Geos 3.3.2 > Is this ok? Should be, yes. > I did not do anything with topology_upgrade.sql, there is nothing > written in the installation doc. That's a bug in the documentation, could you file it on http://trac.osgeo.org please ? > What should I do with it? The same you do with postgis_upgrade.sql everytime you do upgrade from one version to another... load it. > Would you perform a loop to update the topogeom field, so one entry at once? A loop would indeed be preferrable. I saw a noticable speedup when looping from outside the database. > Thanks a lot for your valuable help! Thank you for your feedback, keep me posted ! :) --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] st_geomfromkml problem
On Thu, Feb 23, 2012 at 09:17:11PM -0500, Ahmad Aburizaiza wrote: > So the table has a geom column and a column to store the KML text besides > name and id. I want the geom field to store a polygon from the KML column. > So first I inserted id, name, and KML as text and geom as NULL. Than I ran > the below update SQL statement: > > *Update table1 set geom > = > st_geomfromkml('39.788212,21.424540 > > 39.788212,21.424540'); > * > > > But an error appears that this conflicts with > *enforce_srid_geom*constraint. I removed it and I was able to update > the geom column but it > really does not make sense. The geom I am trying to insert does not > conflict with the constraints. I created the kml polygon in Google Earth > and the srid for that is -1. Please help :) Did you upgrade from 1.x to 2.0 ? If you did you probably did it too early, or not using the correct method (hard upgrade). In 2.0 any negative SRID gets converted to 0 so you'll never meet the check for -1. The hard upgrade procedure updates that constraint. --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] topology example
Thanks. As I have installed from scratch the upgrade files are useless for me, right? (I am getting lost...) Ticket #1611 has been filled I'll try to do the loop today and check. I'll let you know! On 02/24/2012 09:10 AM, Sandro Santilli wrote: On Fri, Feb 24, 2012 at 07:34:54AM +0100, Denis Rouzaud wrote: Hi Sandro, Thanks again. I have a fresh installed for 2 days now. Postgis 2.0 a6 (it is a 7 now) Geos 3.3.2 Is this ok? Should be, yes. I did not do anything with topology_upgrade.sql, there is nothing written in the installation doc. That's a bug in the documentation, could you file it on http://trac.osgeo.org please ? What should I do with it? The same you do with postgis_upgrade.sql everytime you do upgrade from one version to another... load it. Would you perform a loop to update the topogeom field, so one entry at once? A loop would indeed be preferrable. I saw a noticable speedup when looping from outside the database. Thanks a lot for your valuable help! Thank you for your feedback, keep me posted ! :) --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ 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] topology example
On Fri, Feb 24, 2012 at 09:20:13AM +0100, Denis Rouzaud wrote: > As I have installed from scratch the upgrade files are useless for > me, right? Yes. Sorry I assumed you were upgrading. For a new install you just needed to load topology.sql, which you evidently did. --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] topology example
Okay, So I am looping over my 25000 pipes and soon as there is a topology error, the UPDATE distribution.pipes SET topogeom = toTopoGeom(wkb_geometry, ''distrib_topo'', layer) WHERE id = pi.id; throws an error: ERROR: SQL/MM Spatial exception - point not on edge I would like to list all the errors by raising the ids, what is the exception to catch? Thanks.. On 02/24/2012 09:23 AM, Sandro Santilli wrote: On Fri, Feb 24, 2012 at 09:20:13AM +0100, Denis Rouzaud wrote: As I have installed from scratch the upgrade files are useless for me, right? Yes. Sorry I assumed you were upgrading. For a new install you just needed to load topology.sql, which you evidently did. --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ 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] topology example
On Fri, Feb 24, 2012 at 12:03:10PM +0100, Denis Rouzaud wrote: > Okay, > > So I am looping over my 25000 pipes and soon as there is a topology > error, the > UPDATE distribution.pipes SET topogeom = toTopoGeom(wkb_geometry, > ''distrib_topo'', layer) WHERE id = pi.id; > throws an error: > ERROR: SQL/MM Spatial exception - point not on edge Excellent. So a _single_ geometry is enough to reproduce the error. Save both the new geometry (WHERE id = pi.id) and the topology status (dump the schema). If you can, attach the dump and the new input into a ticket. May be worth checking the topology built so far for correctness with ValidateTopology. Any invalidity would be a bug in PostGIS. But not all invalidities are cought by ValidateTopology so there may be a need to do more eyebased checking (linking isn't checked). Usually the invalidity, if any, is within the bounding box of the geometry that fails being installed. > I would like to list all the errors by raising the ids, what is the > exception to catch? A loop _outside_ the database wouldn't need to catch. If you're looping inside, with pl/pgsql, catch OTHERS. --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] st_geomfromkml problem
Ahmad, The question I have for you is why you have your srid as -1. KML spatial_ref is ALWAYS 4326 so I expect any ST_GeomFromKML to come back as srid = 4326. You can't create a KML file that is not 4326 (WGS 84) as I recall. If you were able to in the past, then that's a mistake and perhaps that was corrected in 2.0. > -Original Message- > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On > Behalf Of Sandro Santilli > Sent: Friday, February 24, 2012 3:13 AM > To: PostGIS Users Discussion > Subject: Re: [postgis-users] st_geomfromkml problem > > On Thu, Feb 23, 2012 at 09:17:11PM -0500, Ahmad Aburizaiza wrote: > > > So the table has a geom column and a column to store the KML text > > besides name and id. I want the geom field to store a > polygon from the KML column. > > So first I inserted id, name, and KML as text and geom as > NULL. Than I > > ran the below update SQL statement: > > > > *Update table1 set geom > > = > > > st_geomfromkml('39. > > 788212,21.424540 > > > 39.788212,21.424540 > gon>'); > > * > > > > > > But an error appears that this conflicts with > > *enforce_srid_geom*constraint. I removed it and I was able > to update > > the geom column but it really does not make sense. The geom I am > > trying to insert does not conflict with the constraints. I > created the > > kml polygon in Google Earth and the srid for that is -1. > Please help > > :) > > Did you upgrade from 1.x to 2.0 ? > If you did you probably did it too early, or not using the > correct method (hard upgrade). > In 2.0 any negative SRID gets converted to 0 so you'll never > meet the check for -1. The hard upgrade procedure updates > that constraint. > > --strk; > > ,--o-. > | __/ |Delivering high quality PostGIS 2.0 ! > | / 2.0 |http://strk.keybit.net - http://vizzuality.com > `-o--' > > ___ > 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] topology example
To be sure, we understand each others, here is the few lines of SQL I am using: https://github.com/3nids/qWat/blob/7bc65b5a43ffb9ae5947ede683ff1f2274807972/sql/topology.sql I know I have errors in topology in my pipes. So, is this correct than I can use toTopoGeom for geometries that have incorrect topology? Or should I check them first? Here is the result of validate topology when a part of my pipes is in the topology column: SELECT topology.ValidateTopology('distrib_topo'); validatetopology --- ("face without edges",0,) (1 row) I can dump the db, this will be a few megas, but I would like to be sure I am not doing anything stupid, first On 02/24/2012 12:35 PM, Sandro Santilli wrote: On Fri, Feb 24, 2012 at 12:03:10PM +0100, Denis Rouzaud wrote: Okay, So I am looping over my 25000 pipes and soon as there is a topology error, the UPDATE distribution.pipes SET topogeom = toTopoGeom(wkb_geometry, ''distrib_topo'', layer) WHERE id = pi.id; throws an error: ERROR: SQL/MM Spatial exception - point not on edge Excellent. So a _single_ geometry is enough to reproduce the error. Save both the new geometry (WHERE id = pi.id) and the topology status (dump the schema). If you can, attach the dump and the new input into a ticket. May be worth checking the topology built so far for correctness with ValidateTopology. Any invalidity would be a bug in PostGIS. But not all invalidities are cought by ValidateTopology so there may be a need to do more eyebased checking (linking isn't checked). Usually the invalidity, if any, is within the bounding box of the geometry that fails being installed. I would like to list all the errors by raising the ids, what is the exception to catch? A loop _outside_ the database wouldn't need to catch. If you're looping inside, with pl/pgsql, catch OTHERS. --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ 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] topology example
On Fri, Feb 24, 2012 at 01:52:44PM +0100, Denis Rouzaud wrote: > To be sure, we understand each others, here is the few lines of SQL > I am using: > https://github.com/3nids/qWat/blob/7bc65b5a43ffb9ae5947ede683ff1f2274807972/sql/topology.sql It seems good to me. Do I read it correctly that it exits the loop on first error ? May I suggest you ORDER BY id in the loop ? Or the order would not be predictable. > I know I have errors in topology in my pipes. So, is this correct > than I can use toTopoGeom for geometries that have incorrect > topology? Well, they should be fine for the population of primitives, may give problems in the mapping between original features and topology primitives. In any case the exception you got doesn't reveal such problem. > Here is the result of validate topology when a part of my pipes is > in the topology column: > SELECT topology.ValidateTopology('distrib_topo'); > validatetopology > --- > ("face without edges",0,) > (1 row) Uhm, that's a false positive, I tought I fixed it. Evidently I didn't. Want to file a ticket about it ? > I can dump the db, this will be a few megas, but I would like to be > sure I am not doing anything stupid, first It would help me as well if you reduce it. The important thing is that insertion of your offending geometry keeps hitting the exception. You could for example try dropping edges and then nodes which do not intersect the offending geometry's bounding box. And see if the problem still occurs. Best to backup the whole set first. --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] The PostGIS Blog project
Aggregation would be better for me. The only warning I'd give is you'll also get poorly formed and inefficient queries from blogs like mine, where the implicit cross join is all too common :). I cause enough damage without being aggregated. But hopefully, aggregation would also expose those blogs to additional critique. Best, Steve Stephen Mather Geographic Information Systems (GIS) Manager (216) 635-3243 s...@clevelandmetroparks.com clevelandmetroparks.com -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Paragon Corporation Sent: Friday, February 24, 2012 1:10 AM To: 'PostGIS Users Discussion' Subject: Re: [postgis-users] The PostGIS Blog project Aggregation is better for me. We use serendipity for both BostonGIS and postgresonline.com sites. and we have postgis tags for both that are accessible as separate rss feeds. http://www.bostongis.com/blog/index.php?/feeds/categories/3-postgis-postgres ql.rss http://www.postgresonline.com/journal/feeds/categories/16-postgis.rss Aside from that I think it's a great idea since there are so many bloggers out there that post about PostGIS and probably have those posts tagged and I'm always hunting for them. Thanks, Regina > -Original Message- > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On > Behalf Of Sandro Santilli > Sent: Thursday, February 23, 2012 1:42 PM > To: PostGIS Users Discussion > Subject: Re: [postgis-users] The PostGIS Blog project > > On Thu, Feb 23, 2012 at 12:51:45PM -0500, Pierre Racine wrote: > > I don't see you have a "postgis" category. Only "GIS"... > > You're right! > But I have a postgis tag: > http://strk.keybit.net/blog/tag/postgis/feed/ > > NOTE: wordpress is free software, don't let Blogger grab you. > > --strk; > > ,--o-. > | __/ |Delivering high quality PostGIS 2.0 ! > | / 2.0 |http://strk.keybit.net - http://vizzuality.com > `-o--' > > ___ > 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 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] topology example
Ok, I added ORDER BY https://github.com/3nids/qWat/blob/3c0c6728b940dd4ab2921dee104cd054a711d60f/sql/topology.sql You effectively read correctly that the loop is ended as soon as an exception is raised, since I still didn't manage to do it properly. I filled ticket 1611, http://trac.osgeo.org/postgis/ticket/1612 Sorry, for the lack of info in, but I don't really understand the matter. Also, I noticed something strange. Nothing is filled into the topogeom column. When ordered, first error is raised at id 102, but the first 100 lines have empty topogeom. In the function, if an error is raised nothing is committed? I wrote a small SQL with two geometries where I have the error. https://github.com/3nids/qWat/blob/38cde48d509d9b9a179eea1fd4db5bbd67121dcf/sql/strk.demo.sql I putted a some " !!! " in front of drop schema to prevent any harm to some of your schemasjust in case! Greetings, Denis On 02/24/2012 02:08 PM, Sandro Santilli wrote: On Fri, Feb 24, 2012 at 01:52:44PM +0100, Denis Rouzaud wrote: To be sure, we understand each others, here is the few lines of SQL I am using: https://github.com/3nids/qWat/blob/7bc65b5a43ffb9ae5947ede683ff1f2274807972/sql/topology.sql It seems good to me. Do I read it correctly that it exits the loop on first error ? May I suggest you ORDER BY id in the loop ? Or the order would not be predictable. I know I have errors in topology in my pipes. So, is this correct than I can use toTopoGeom for geometries that have incorrect topology? Well, they should be fine for the population of primitives, may give problems in the mapping between original features and topology primitives. In any case the exception you got doesn't reveal such problem. Here is the result of validate topology when a part of my pipes is in the topology column: SELECT topology.ValidateTopology('distrib_topo'); validatetopology --- ("face without edges",0,) (1 row) Uhm, that's a false positive, I tought I fixed it. Evidently I didn't. Want to file a ticket about it ? I can dump the db, this will be a few megas, but I would like to be sure I am not doing anything stupid, first It would help me as well if you reduce it. The important thing is that insertion of your offending geometry keeps hitting the exception. You could for example try dropping edges and then nodes which do not intersect the offending geometry's bounding box. And see if the problem still occurs. Best to backup the whole set first. --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ 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] topology example
On Fri, Feb 24, 2012 at 03:20:11PM +0100, Denis Rouzaud wrote: > Ok, I added ORDER BY > https://github.com/3nids/qWat/blob/3c0c6728b940dd4ab2921dee104cd054a711d60f/sql/topology.sql > > You effectively read correctly that the loop is ended as soon as an > exception is raised, since I still didn't manage to do it properly. That's actually good. If the goal is finding the bug :) The smaller topology you have at time of exception, the better. > Also, I noticed something strange. Nothing is filled into the > topogeom column. When ordered, first error is raised at id 102, but > the first 100 lines have empty topogeom. In the function, if an > error is raised nothing is committed? The version I saw didn't look like in need to rollback, no. But I could have misread it. > I wrote a small SQL with two geometries where I have the error. > https://github.com/3nids/qWat/blob/38cde48d509d9b9a179eea1fd4db5bbd67121dcf/sql/strk.demo.sql > I putted a some " !!! " in front of drop schema to prevent any harm > to some of your schemasjust in case! That's great ! Could you put in another ticket please ? I was actually out of topology tickets for 2.0 ;) Not sure when I'll have time to look at it but it's really an excellent work you've done so far. If you feel like doing more you may try adding the following line by the start of topology/topology.sql.in.c, running 'make' again and loading the topology/topology_upgrade.sql: #define POSTGIS_TOPOLOGY_DEBUG 1 By the way, did "make check" completed cleanly for you ? --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] topology example
> > > The version I saw didn't look like in need to rollback, no. > But I could have misread it. > Agree, so I do not understand why there nothing filled. > > > I wrote a small SQL with two geometries where I have the error. > > > https://github.com/3nids/qWat/blob/38cde48d509d9b9a179eea1fd4db5bbd67121dcf/sql/strk.demo.sql > > I putted a some " !!! " in front of drop schema to prevent any harm > > to some of your schemasjust in case! > > That's great ! Could you put in another ticket please ? > I was actually out of topology tickets for 2.0 ;) > Three in a row ;) http://trac.osgeo.org/postgis/ticket/1613 > > Not sure when I'll have time to look at it but it's really an excellent > work you've done so far. If you feel like doing more you may try > adding the following line by the start of topology/topology.sql.in.c, > running 'make' again and loading the topology/topology_upgrade.sql: > > #define POSTGIS_TOPOLOGY_DEBUG 1 > I'll try this on monday. > > By the way, did "make check" completed cleanly for you ? > I don't remember what went out. I'll check this on monday morning and give you feedback. Thanks a lot for your time and work! Maybe, everything will autofix during the weekend ... Greetings, Denis ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Correct TIN input format for ST_AsGML
Hi everyone, I am having problems getting ST_AsGML to work with TIN objects. I have the following: SELECT ST_AsGML(ST_GeomFromEWKT( 'TIN ((( 0 0 0, 0 0 1, 0 1 0, 0 0 0 )), (( 0 0 0, 0 1 0, 1 1 0, 0 0 0 )))' )); However, it gives me an error: ERROR: lwgeom_to_gml2: 'Tin' geometry type not supported Then, I try the following: SELECT ST_AsGML(ST_GeomFromEWKT( 'TINS(TIN ((( 0 0 0, 0 0 1, 0 1 0, 0 0 0 )), (( 0 0 0, 0 1 0, 1 1 0, 0 0 0 ' )); This time I have a parse error: ERROR: parse error - invalid geometry Does anyone know the correct format for TIN geometry to work with ST_AsGML? Any help is greatly appreciated! - Edison Tam NGC SWE US -- View this message in context: http://postgis.17.n6.nabble.com/Correct-TIN-input-format-for-ST-AsGML-tp4503527p4503527.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] topology example
On 2/24/2012 10:44 AM, Denis Rouzaud wrote: The version I saw didn't look like in need to rollback, no. But I could have misread it. Agree, so I do not understand why there nothing filled. If your are running a stored procedure and you error out of it, the whole stored procedure is always run as a transaction, an on error it does an implicit rollback. If you want to verify that the stored procedure is run in an transaction, just try and do a vacuum in the stored procedure -- you can't. Or write a stored procedure that creates a table inserts a couple of rows, then does a RAISE EXCEPTION. You should see that the table does not exist after this. -Steve > I wrote a small SQL with two geometries where I have the error. > https://github.com/3nids/qWat/blob/38cde48d509d9b9a179eea1fd4db5bbd67121dcf/sql/strk.demo.sql > I putted a some " !!! " in front of drop schema to prevent any harm > to some of your schemasjust in case! That's great ! Could you put in another ticket please ? I was actually out of topology tickets for 2.0 ;) Three in a row ;) http://trac.osgeo.org/postgis/ticket/1613 Not sure when I'll have time to look at it but it's really an excellent work you've done so far. If you feel like doing more you may try adding the following line by the start of topology/topology.sql.in.c, running 'make' again and loading the topology/topology_upgrade.sql: #define POSTGIS_TOPOLOGY_DEBUG 1 I'll try this on monday. By the way, did "make check" completed cleanly for you ? I don't remember what went out. I'll check this on monday morning and give you feedback. Thanks a lot for your time and work! Maybe, everything will autofix during the weekend ... Greetings, Denis ___ 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] topology example
On Fri, Feb 24, 2012 at 12:59:48PM -0500, Stephen Woodbridge wrote: > On 2/24/2012 10:44 AM, Denis Rouzaud wrote: > > > >The version I saw didn't look like in need to rollback, no. > >But I could have misread it. > > > > > >Agree, so I do not understand why there nothing filled. > > If your are running a stored procedure and you error out of it, the > whole stored procedure is always run as a transaction, an on error > it does an implicit rollback. But he's supposedly catching the exception from within the function. --strk; ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Rv: Postgis for Postgres 64 bits
Discussions on 32 bit vs 64 bit Postgresql: http://archives.postgresql.org/pgsql-performance/2004-11/msg00201.php http://postgresql.1045698.n5.nabble.com/64-bit-vs-32-bit-performance-backwards-td2057238.html See document on 5 steps to postgresql performance by Josh Berkus: http://www.pgexperts.com/document.html?id=36 "64 bit performance can be 5-20% faster" Doug Doug Newcomb USFWS Raleigh, NC 919-856-4520 ext. 14 doug_newc...@fws.gov - The opinions I express are my own and are not representative of the official policy of the U.S.Fish and Wildlife Service or Dept. of the Interior. Life is too short for undocumented, proprietary data formats. Andy Colson Sent by: postgis-users-boun...@postgis.refractions.net 02/04/2012 02:52 PM Please respond to PostGIS Users Discussion To PostGIS Users Discussion cc Subject Re: [postgis-users] Rv: Postgis for Postgres 64 bits On 02/04/2012 01:14 PM, Darrel Maddy wrote: > Dear Regina, > > I would be happy to contribute $100 into any fund which secures a 64-bit windows build of PostGIS2 -especially as I am about to install it on a machine with 64Gb RAM :) > > Count now at 1 ? > > Thanks > > Darrel > http://www.ncl.ac.uk/gps/staff/profile/darrel.maddy#tab_profile > > I really wonder if its faster. I'd bet not. If you consider the architecture, I'd bet, for all but the very largest of datasets, 32 bit PG running on top 64 bit os is faster. Consider: you want the os disk cache to be as large as possible. You want a bunch of shared mem and I'd bet around 2 gig would be a sweet spot. More shared mem wouldn't necessarily be a benefit. (There are upper bounds recommended for shared_buffers, especially on windows). PG spawns a separate client for each connection, so each one of them could access up to 2 gig. (so things like work_mem could be large). And last, 32bit programs are faster because they move less data around (32bit pointers instead of 64bit). Taken all together PostMaster + (clients * postgres.exe) + os cache could easly be more than 2 gig using 32 bit PG on 64 bit windows. The only benefit of using 64bit PG would be a larger shared mem. The online manual says: "The useful range for shared_buffers on Windows systems is generally from 64MB to 512MB" http://www.postgresql.org/docs/9.1/static/runtime-config-resource.html The best use of ram, is as a disk cache, which 64 bit windows should use as much as possible (well, I assume). After that there is nothing in Postgres (Per Client) that is going to run faster with more than 2 gig of ram. Has anyone ever done any benchmarks? -Andy ___ 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] st_geomfromkml problem
Thanks a lot to both of you. I changed the SRID to 4326 and it is working. *--- Ahmad Aburizaiza PhD student at George Mason University Geography and Geoinformation Science Department Fairfax, VA, USA Tel : +1-703-981-0354* On Fri, Feb 24, 2012 at 7:41 AM, Paragon Corporation wrote: > Ahmad, > > The question I have for you is why you have your srid as -1. > > KML spatial_ref is ALWAYS 4326 so I expect any ST_GeomFromKML to come back > as srid = 4326. You can't create a KML file that is not 4326 (WGS 84) as I > recall. If you were able to in the past, > then that's a mistake and perhaps that was corrected in 2.0. > > > > -Original Message- > > From: postgis-users-boun...@postgis.refractions.net > > [mailto:postgis-users-boun...@postgis.refractions.net] On > > Behalf Of Sandro Santilli > > Sent: Friday, February 24, 2012 3:13 AM > > To: PostGIS Users Discussion > > Subject: Re: [postgis-users] st_geomfromkml problem > > > > On Thu, Feb 23, 2012 at 09:17:11PM -0500, Ahmad Aburizaiza wrote: > > > > > So the table has a geom column and a column to store the KML text > > > besides name and id. I want the geom field to store a > > polygon from the KML column. > > > So first I inserted id, name, and KML as text and geom as > > NULL. Than I > > > ran the below update SQL statement: > > > > > > *Update table1 set geom > > > = > > > > > st_geomfromkml('39. > > > 788212,21.424540 > > > > > 39.788212,21.424540 > > gon>'); > > > * > > > > > > > > > But an error appears that this conflicts with > > > *enforce_srid_geom*constraint. I removed it and I was able > > to update > > > the geom column but it really does not make sense. The geom I am > > > trying to insert does not conflict with the constraints. I > > created the > > > kml polygon in Google Earth and the srid for that is -1. > > Please help > > > :) > > > > Did you upgrade from 1.x to 2.0 ? > > If you did you probably did it too early, or not using the > > correct method (hard upgrade). > > In 2.0 any negative SRID gets converted to 0 so you'll never > > meet the check for -1. The hard upgrade procedure updates > > that constraint. > > > > --strk; > > > > ,--o-. > > | __/ |Delivering high quality PostGIS 2.0 ! > > | / 2.0 |http://strk.keybit.net - http://vizzuality.com > > `-o--' > > > > ___ > > 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 > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users