Re: [postgis-users] pgsql2shp
On 03/07/12 07:14, James Sewell wrote: Hello, When I run pgsql2shp like so: pgsql2shp -u plm25 -g the_geom database table I get the following output: Xcolumn number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 column number -1 is out of range 0..1 This continues in blocks for (I assume) each row in the table. When I go to load the shape I get null values for all rows as follows: INSERT INTO table (the_geom) VALUES (NULL); My versions are: PostgreSQL 9.1.4 on sparc-sun-solaris2.10, compiled by cc: Sun C 5.11 SunOS_sparc 145354-05 2011/09/13, 64-bit POSTGIS=2.0.1 r9979 GEOS=3.3.5-CAPI-1.7.5 PROJ=Rel. 4.8.0, 6 March 2012 GDAL=GDAL 1.9.1, released 2012/05/15 GDAL_DATA not found LIBXML=2.7.8 LIBJSON=UNKNOWN R ASTER Any ideas? This is a fresh table, not migrated form an old version. That's strange - I don't see that error message within the PostGIS codebase at all, so maybe it is coming from libpq. Do you see that error with just that one table, or with a shapefile you have loaded using shp2pgsql? Any chance of sharing your table schema with us, e.g. the output of \d table in psql? ATB, Mark. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Super weird problem: Cannot insert more than ~300 rows (or ~100 kb) into a table
On 03/07/12 12:34, René Fournier wrote:
Well, I did some more testing, and found out that the failed query and
subsequent crash of the postgresql client isn't caused by the lat/lng
coordinates... It happens consistently after a certain number of rows
(and/or amount of data) is inserted into the table. Usually close to 300
rows (or 96KB of data according to psql).
postgis_full_version
--
POSTGIS=1.5.4 GEOS=3.3.2-CAPI-1.7.2 PROJ=Rel. 4.8.0, 6 March 2012
LIBXML=2.7.8 USE_STATS
So, very strange... If I insert programmatically 280-290 rows (varies
seemingly depending on the amount of data in each row), then try to add
rows one at a time, here's what happens:
mydb=# select count(*) from addresses;INSERT INTO addresses (
account_id, territory_id, location ) VALUES ( 1, 75,
ST_GeomFromText('POINT(-114.267388 51.089941)') );
count
---
345
(1 row)
INSERT 0 1
mydb=# select count(*) from addresses;INSERT INTO addresses (
account_id, territory_id, location ) VALUES ( 1, 75,
ST_GeomFromText('POINT(-114.267388 51.089941)') );
count
---
346
(1 row)
The connection to the server was lost. Attempting reset: Failed.
! select count(*) from addresses;INSERT INTO addresses (
account_id, territory_id, location ) VALUES ( 1, 75,
ST_GeomFromText('POINT(-114.267388 51.089941)') );
You are currently not connected to a database.
If I quit and restart psql, same error occurs. Only if I drop the table
can I insert more rows — but again, only up to around 300 rows or 100 kb
(not sure where the limit is)... So, it's not dependent on the kind of
coordinates or row data, just the number of inserts and/or the amount of
data. Any ideas what is causing the problem here?
Strange - from what you're saying, it sounds as if it could be to do
with the index splitting code, but without a backtrace
(http://trac.osgeo.org/postgis/wiki/DevWikiGettingABackTrace) it's
tricky to say exactly.
One small nit - I notice you've got a geography column but you're using
ST_GeomFromText() rather than ST_GeogFromText() which is likely
introducing an extra cast somewhere. Does changing to ST_GeogFromText()
help prevent the crash at all?
ATB,
Mark.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Super weird problem: Cannot insert more than ~300 rows (or ~100 kb) into a table
On 03/07/12 13:20, René Fournier wrote:
So, it seems that the table gets full and corrupted to some extent.
After my import script inserts ~290 rows, and then postgres crashes...
mydb=# select count(*) from addresses;INSERT INTO addresses (
account_id, territory_id, location ) VALUES ( 1, 75,
ST_GeomFromText('POINT(-114.267388 51.089941)') );
count
---
284
(1 row)
The connection to the server was lost. Attempting reset: Failed.
So, can't insert any more rows...
! delete from addresses where id 50;
You are currently not connected to a database.
Postgres client/connection is crashed.
! \q
Tue Jul 03 14:15:28 -- rene /opt/local/var/db:: psql -U postgres
mydb psql (9.1.4)
Type help for help.
mydb=# delete from addresses where id 50;
DELETE 234
Deleting rows works...
mydb=# select count(*) from addresses;INSERT INTO addresses (
account_id, territory_id, location ) VALUES ( 1, 75,
ST_GeomFromText('POINT(-114.267388 51.089941)') );
count
---
50
(1 row)
The connection to the server was lost. Attempting reset: Failed.
SELECT and DELETE work, but I can't insert any new rows, until...
! \q
Tue Jul 03 14:15:39 -- rene /opt/local/var/db:: psql -U postgres
mydb psql (9.1.4)
Type help for help.
mydb=# vacuum;vacuum full;vacuum full analyze;
VACUUM
VACUUM
NOTICE: no notnull values, invalid stats
VACUUM
mydb=# select count(*) from addresses;INSERT INTO addresses (
account_id, territory_id, location ) VALUES ( 1, 75,
ST_GeomFromText('POINT(-114.267388 51.089941)') );
count
---
50
(1 row)
INSERT 0 1
mydb=#
So, it appears there's some weird corruption going on... Still, not sure
what to try next. My PostGIS is via Macports, not sure how to enable the
debug mode...
Hi René,
I think that you need to create a new bug on the PostGIS bug tracker and
upload a file that causes the crash on your system, i.e. it can be run
using psql -d postgis_db -f crash.sql so that we can try and reproduce
what you are seeing.
Also as a matter of interest, do you see anything interesting in the
PostgreSQL log file at the time of the crash?
ATB,
Mark.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostgreSQL/PostGIS x64 Error on PostGIS Install
On 07/06/12 14:00, Donald Kerr wrote: Thanks, Regina. After uninstalling, I tried a fresh install of PostgreSQL followed by a fresh install of PostGIS. The difference this time is that I did not use the existing data structure and started with a new one. The installation went well and without error. I uninstalled then repeated the above process with my existing data structure and got the same error as before. The data structure was created using the 32 bit version of PostgreSQL/PostGIS: postgresql-9.1.3-1-windows.exe (Not 9.1.4). Is there an incompatibility between 64 bit and 32 bit data structures? Any pointers appreciated. Ah yes. Due to the different variable sizes between 64 and 32 bits, the underlying binary structures on disk will be different and therefore incompatible. HTH, Mark. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] shp2pgsql-gui and option -S (Generate simple instead of MULTI geometries) / Ticket #660?
On 30/01/12 08:32, Sandro Santilli wrote: On Mon, Jan 30, 2012 at 07:40:01AM +0100, Stefan Keller wrote: 2012/1/27 Sandro Santillis...@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! I've just committed this feature to SVN trunk as r9027. Unfortunately due to the difference in architecture, it's not going to be possible to backport to 1.5. 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] pgsql2shp
On 01/12/11 08:32, toni hernández wrote: Hi, I have some issues with pgsql2shp. I am using postgis 1.5.3 over postgresql 9.0 on windows. The database I am working with is utf8. I have several spatial tables and I want some of them to be exported to a shape file. pgsql2shp seems to be working fine but when I load the generated shape files, then they appear to be empty. No error thrown (on OpenJump). I have been testing it for a while and I came to realize that pgsql2shp does not like tables that only have one field (the spatial one). I can not export a table (any) with only one column but if I add a column to that very table (type character varying) then the exportation works fine and I can load the shp file perfectly At first I thought it was something about the encoding but now I am lost. Any ideas how to solve this? thanks Hi Toni, I don't suppose you've been able to try pgsql2shp from one of the PostGIS 2.0 pre-releases at all? It's had a fairly hefty rewrite and so you might find that this will have solved the issue. 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] character encoding problems
On 30/11/11 02:24, Clay, Bruce wrote: I trying to learn more about natural language processing and language translation I have installed the English version of WordNet in Postgres without any problems. I downloaded dictionaries from a varity of site such as are used in OpenOffice / WinEdt. When I try to build a table from several of the different languages I get the following error ERROR: invalid byte sequence for encoding UTF8: 0x82 I checked the encoding and it is indeed set up for Unicode-8. I tried to create databases using a variety of other encoding types such as WIN1252 and others and I got the same error message from all of them except SQL_ASCII. When I created the database using SQL_ASCII I recieved the warning that the database could only store 7 bit data. When I loaded the data in this database I did not have any errors and when I look at the data it seems to be the same as in the original text file. Is there a proper encoding type that I should use to load the word lists so they can interoperate with the WordNet dataset that happily uses the UTF8 encoding? Bruce Hi Bruce, This isn't strictly a PostGIS question, so you'd be better off re-posting to the pgsql-general mailing list to get some answers. However, from what you mention above it seems that the extra dictionaries you are downloading are not in UTF8 encoding and so may require conversion upon import. You can potentially use SQL_ASCII as a workaround, but I would highly recommend that you don't do this, since then you end up with data in a mixture of random encodings that you will never be able to output correctly across all platforms. 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: Operation on two geometries with different SRIDs
On 07/11/11 14:07, Stefan Schwarzer wrote: Now, trying to import it with: shp2pgsql -s EPSG:26191 -I -D morocco.shp morocco morocco.sql psql -U my_database morocco.sql Hi Stefan, The SRID argument to shp2pgsql should just be a plain numeric field, and not have the EPSG prefix in front of it, e.g.: shp2pgsql -s 26191 -I -D morocco.shp morocco morocco.sql This should generate you a file with the SRID 26191 which should load without any further issues. HTH, 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] Indexes used in 1.3.3 but not in 1.5.3
On 20/10/11 16:31, Frederic Junod wrote: The database was imported with postgis_restore.pl. I've also tried to reindex the database. Note that on both instances, if the st_buffer is removed the indexes are used. What's happening ? Hi Frederic, You'll have to post the EXPLAIN ANALYZE output if you want more help with this one. Also note that just because an index is there, it may not always be used because sequential scans can be used in a lot of cases. HTH, 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] Trouble with shp2pgsql-gui?
On 05/10/11 14:09, MarkW wrote: John - SET CLIENT is the beginning of a larger SQL statement. The error says the SQL statement failed, ...sql begins: and then later down the error dump indicates a connectivity issue - which you later identified as the probable cause. So it is not saying the encoding is the failure, just that that was first line in SQL block submitted to pg server. Mark Yes, that's correct. Did you check your connection using the conncetion test button before trying to load your data? If the connection test succeeds but the header SQL doesn't, perhaps you're trying to load the data into a database that isn't PostGIS enabled? 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] [postgis-devel] Vote On Project Sponsorship
On 09/09/11 11:45, Paragon Corporation wrote: So does this mean a +1 from you and Mark for project sponsorship as long as we focus whatever fundings come in to 1) Improved testing, building infrastructure 2) That includes possible cloud hosting for windows 32/64 bit (actually all we need is 1 64-bit box). I'm runnning a 64-bit Windows 7 and build fine for 32-bit. This would be shared with OSGEO (per Frank's suggestion) 3) Money for events -- code sprints, subsidizing for PostGIS developers to attend events, like those Europeans we are so fund of that we are going to miss at this year's FOSS4G. Hmm possibly in the future a small PostGIS code sprint would be nice :) 4) One other thing I forgot to mention I think money could be well spent on is the accredation to get our newer versions of PostGIS to be OGC certified. This I think is a big selling point for large companies and much easier sell for consultants consulting for large companies if we have a piece of paper saying it instead of just that we follow the specs. Thanks, Regina I do think that if we initially set a narrow scope for what the funds could be used for (similar to the above list) then that would be good. I'm tempted to vote +1, except for the fact that I know that I would be struggling to find any extra time to put into the management side of this... maybe a +1 in principle? 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] [postgis-devel] Vote On Project Sponsorship
On 02/09/11 21:36, Paragon Corporation wrote: Chris, I'm cc'ing regular users on this since it was several of them who had asked for this and that is the main reason I am pushing it. 1) I'm a bit pessimistic we would garner enough funds to fund a particular feature. So lets just not go there. It's talking about stuff that may never happen And those things may best be dealt with between two individuals if we can't come to an agreement. 2) I was thinking the money could be spent to support plane tickets and fees to say go to FOSS4G etc that many of our developers wouldn't be able to afford on their own. 3) I was thinking Refractions should be reimbursed in some way for providing hosting, build bot etc. And if we did have extra -- perhaps we can have build bots for other platforms So that these major changes we make aren't quite so painful (we catch them much earlier on). I do not see this as a free money, but more as a simple answer to people when they ask If I want to fund the project, who do I give money too? This is an extremely frustrating question to not be able to answer and I don't think any of us would Feel comfortable saying Give it to me directly I agree that while assigning financial resource to development tasks is going to be tricky and require quite a bit of time, I do like the idea of things like sponsorship for travel to code sprints (hey - I'm in Europe!) and also some funding some kind of build infrastructure. For example, I think it would be useful to fund Win32 and Win64 cloud instances so that I can help Regina with the Windows builds when she gets stuck. 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] compile on solaris 11 sparc
On 06/09/11 12:54, Doug McComber wrote: Didn't work (E flag). I ended up dumping Solaris for FreeBSD. You see I always fall for this trap. Every time I dust of my Sunfire I get enchanted with the idea of using Solaris. But, and especially with Oracle now, Solaris is not very free or open. And I always end up running into some hurdle that isn't worth struggling with when there are plenty of good alternatives (Debian and FreeBSD come to mind). Of course I am ranting here so I'll stop. :) Thanks for your and everyone else's help in trying to solve this. Doug Well just for the record - the issue here was that the person who built your PostgreSQL binaries used the native Sun compiler on their system hence embedding this as the default CC in the PGXS Makefile used by PostGIS. If you still wanted to use Solaris then using GCC to build both PostgreSQL and PostGIS on your server should give you something that would work. HTH, 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] compile on solaris 11 sparc
On 02/09/11 18:02, Doug McComber wrote: I've got this in my profile: # 64 bit compiling CC=gcc CXX=g++ CPP=gcc CFLAGS=-m64 CPPFLAGS=-m64 CXXFLAGS=-m64 # 3rd party libray LDFLAGS=-L/usr/local/lib and ran source ~/.profile Despite that I found I had to add some of that to the configure command (shouldn't have to log out after sourcing profile right?) like so: ./configure CC=gcc CFLAGS=-m64 CPPFLAGS=-m64 CXXFLAGS=-m64 LDFLAGS=-L/usr/local/lib --with-geosconfig=/usr/local/bin/geos-config This completes without error. The only warnings I saw were about dblatex and CUnit. The final output of configure being: PostGIS is now configured for sparc-sun-solaris2.11 -- Compiler Info - C compiler: gcc -m64 C++ compiler: g++ -m64 -- Dependencies -- GEOS config: /usr/local/bin/geos-config GEOS version: 3.3.0 PostgreSQL config: /usr/postgres/9.0-pgdg/bin/64/pg_config PostgreSQL version: PostgreSQL 9.0.4 PROJ4 version: 47 Libxml2 config: /usr/bin/xml2-config Libxml2 version: 2.7.6 PostGIS debug level: 0 Documentation Generation xsltproc: /usr/bin/xsltproc xsl style sheets: dblatex: convert: /usr/bin/convert Make starts out well but dies with an error complaining about not finding SunStudio! gcc -m64 -fPIC -DPIC -Wall -Wmissing-prototypes -c -o g_util.o g_util.c gcc -m64 -fPIC -DPIC -Wall -Wmissing-prototypes -c -o lwgeodetic.o lwgeodetic.c gcc -m64 -fPIC -DPIC -Wall -Wmissing-prototypes -c -o lwtree.o lwtree.c gcc -m64 -fPIC -DPIC -Wall -Wmissing-prototypes -ffloat-store -c -o lwspheroid.o lwspheroid.c ar rs liblwgeom.a measures.o box2d.o ptarray.o lwgeom_api.o lwgeom.o lwpoint.o lwline.o lwpoly.o lwmpoint.o lwmline.o lwmpoly.o lwcollection.o lwcircstring.o lwcompound.o lwcurvepoly.o lwmcurve.o lwmsurface.o lwutil.o lwalgorithm.o lwgunparse.o lwgparse.o lwsegmentize.o wktparse.tab.o lex.yy.o vsprintf.o g_box.o g_coord.o g_geometry.o g_ptarray.o g_serialized.o g_util.o lwgeodetic.o lwtree.o lwspheroid.o ar: creating liblwgeom.a make[1]: Leaving directory `/home/doug/sources/postgis-1.5.3/liblwgeom' make -C postgis make[1]: Entering directory `/home/doug/sources/postgis-1.5.3/postgis' /opt/studio11/SUNWspro/bin/cc -Xa -E -traditional-cpp postgis.sql.in.c | grep -v '^#' postgis.sql.in /bin/sh: line 1: /opt/studio11/SUNWspro/bin/cc: not found make[1]: *** [postgis.sql.in] Error 1 make[1]: *** Deleting file `postgis.sql.in' make[1]: Leaving directory `/home/doug/sources/postgis-1.5.3/postgis' make: *** [postgis] Error 2 This is a Solaris 11 box I should mention and gcc was installed from the Oracle Solaris 11 repository as opposed to a 3rd-party package or from source. I don't have Sun Studio installed (never did) and other programs compiled without problem (Proj 4 and GEOS). This error /opt/studio11/SUNWspro/bin/cc: not found appears to be PostGIS specific (according to Google anyway). Suggestions? Right, so currently the .sql files are generated using the C pre-processor and so uses the CPP variable in addition to the ones defined above. I'm not sure what the correct command line should be to invoke the pre-processor in Sun's compiler is though. I think we need to start thinking about ripping the CPP code out and finding something more suitable instead, particularly when thinking about building other platforms such as Win32/64. I'm not exactly sure what the replacement should be though :/ 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: could not find function isvalidreason in file /usr/local/postgresql-8.2.20/lib/postgis-1.4.so
On 09/08/11 09:04, ml ml wrote: Hello, i am trying to restore a postgres dump on a freshly installed Debian Squeeze Box. I am using postgres 8.2.20, postgis 1.4.2 and geos.3.3.0 from source. Postgresql 8.2.20: ./configure --prefix=/usr/local/postgresql-8.2.20 --with-perl --with-openssl --with-libxslt --with-libxml Geos: ./configure --prefix=/usr/local/geos-3.3.0 Postgis: ./configure --with-pgconfig=/usr/local/postgresql-8.2/bin/pg_config --with-geosconfig=/usr/local/geos-3.3/bin/geos-config Restore: -- /usr/local/postgresql-8.2/bin/pg_restore -U pgsql -d test --exit-on-error --no-owner /root/test_dev-Sat-18.bin pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 43383; 1255 3510237 FUNCTION st_isvalidreason(geometry) pgsql pg_restore: [archiver (db)] could not execute query: ERROR: could not find function isvalidreason in file /usr/local/postgresql-8.2.20/lib/postgis-1.4.so Command was: CREATE FUNCTION st_isvalidreason(geometry) RETURNS text AS '$libdir/postgis-1.4', 'isvalidreason' LANGUAGE c IMMUTAB... I read that st_isvalidreason needs goes= 3.1 which i actually use. I used to use geos 3.0 from the debian squeeze repo, which i replaced (see source config above) Kind Regards, Mario Hi Mario, That all looks as if it should work. What does the following command show? ldd /usr/local/postgresql-8.2.20/lib/postgis-1.4.so That will show you which version of GEOS your custom-built PostGIS is built against. HTH, 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] PostGIS 2.0 installation error
On 03/07/11 23:12, Denis Rykov wrote: I'm using PostgreSQL 9.0.4 on i686-pc-linux-gnu, compiled by GCC gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 32-bit Hmmm. And no copies of 9.1 beta anywhere on that box either? 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] PostGIS 2.0 installation error
On 03/07/11 16:41, Denis Rykov wrote: |ERROR: could not load library/usr/lib/postgresql/9.0/lib/postgis-2.0.so http://postgis-2.0.so: /usr/lib/postgresql/9.0/lib/postgis-2.0.so http://postgis-2.0.so: undefined symbol: DirectFunctionCall3Coll | More detailed information at : http://gis.stackexchange.com/questions/11759/postgis-installation-error That's a strange error - it almost implies that a PostgreSQL 9.0-only macro has been used somewhere in the source code. Which version of PostgreSQL are you building against? 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] PostGIS 2.0 Install Error
On 27/06/11 19:45, Chris Seker wrote: I have downloaded and installed PROJ4.6.1 and PostGIS-2.0 SVN. Still having the following error. sh-3.2$ psql -d template_postgis20 -f postgis2.sql SET BEGIN psql:postgis2.sql:65: ERROR: could not load library /usr/pgsql-9.1/lib/postgis-2.0.so: /usr/pgsql-9.1/lib/postgis-2.0.so: undefined symbol: pj_set_searchpath psql:postgis2.sql:70: ERROR: current transaction is aborted, commands ignore Any ideas? That still looks as if it's referencing an older version of PROJ.4 that doesn't have pj_set_searchpath() in it :( What does ldd /usr/pgsql-9.1/lib/postgis-2.0.so show? 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] Optimise this query, point in polygon on quite a large scale
On 26/06/11 22:02, Louis Holford wrote: So...I have a large list of points (~30,000). I need to count the number of points that overlap a polygon (or multiple polyongs e.g. 100's). Each polygon represents part of a grid that overlaps the envelope of the list of points, so I need to know the count of points per grid cell. Currently I can generate each grid cell in memory and feed them to a query using ST_GeomFromText. Now, I know this takes a long time to complete and is not optimal. I would like any opinions as to what the best solution to implementing the query would be. I am thinking along the lines of generating the full grid as a table with either each cell stored separately or the whole grid as a single multipolygon and then performing the ST_Intersects part of the query. And also I know I should generate an index to help the query planning/search. Hi Louis, Yes - this is a good starting point. In-memory grids cannot use the index and so you'll need to materialise the resulting grid somehow whether that's just in a temporary table or somewhere more permanent, and then build an index on it in order to get worthwhile performance. It's likely you'll be looking at a combination of a cross join based upon the operator, ST_Intersects() and a GROUP BY over a SUM() aggregate in order to generate the totals for each grid square. HTH, 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] Failed POST GIS on Re-boot.
On 23/06/11 20:25, Chris Seker wrote: Hi all. I am running PostgreSQL 9.1 and PostGIS2.0 under CENTOS. After install, the server worked fine and we spent about 2 weeks building databases, etc. Then after some routine hardware maintenance and re-boot, we get the following error. Top of Form *SQL **select * from oxbuildings2 limit 25 offset 0** failed : **ERROR: could not load library /usr/pgsql-9.1/lib/postgis-1.5.so: /usr/pgsql-9.1/lib/postgis-1.5.so: undefined symbol: pj_set_searchpath *** Bottom of Form Anyone have an idea? I am thinking that it’s a simple ENV setting. pj_set_searchpath() was something that didn't exist in the really early versions of PROJ.4 - is there a chance you could have accidentally downgraded your version of PROJ.4? What does 'proj -v' show? 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] statistics on a spatial index
On 09/06/11 20:08, Oleksiy Karpenko wrote: Hi, How can I obtain various statistics on an existing index in the database? For example, number of levels, average MBR size by each dimension, and so on? If you wish to start looking at the R-Tree structure then I would start by looking at Oleg and Teodor's gevel utility: http://www.sai.msu.su/~megera/postgres/gist/ One day we should probably think about adding something like this into the main codebase... HTH, 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] Infinite loop in st_intersects - because of incorrect data out of st_transform?
On 09/06/11 12:53, Sandro Santilli wrote: Every new release in the 3.x.x series is backward compatible at the C-API level. PostGIS uses the C-API. True. Plus new GEOS releases are invariably better (read as: more robust) than their predecessors, so if you can go for this option I would definitely recommend it. 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] postgis-1.5.2 build failure on HP-UX
On 06/05/11 17:47, MUHAMMAD ASIF wrote: I am trying to build 64 bit binaries for postgis-1.5.2 on HP-UX B.11.31 (ia64 hp Integrity rx2800 i2). HP compiler was giving the following error message i.e. make -C postgismake[1]: Entering directory `/home/edb/AS90/postgis/postgis-1.5.2/postgis'cc +DD64 -Ae -E -traditional-cpp postgis.sql.in.c | grep -v '^#' postgis.sql.incc: error 1914: bad form for `-t' optionmake[1]: *** [postgis.sql.in] Error 1make[1]: *** Deleting file `postgis.sql.in'make[1]: Leaving directory `/home/edb/AS90/postgis/postgis-1.5.2/postgis' To fix it, I just removed the -traditional-cpp option that is related to GCC. Build seems fine. It can be compiled with non gcc compilers then why is there option -traditional-cpp hard coded related to GCC. Thanks. I think Sandro was the person who did this part of the build system, so perhaps he may know? I also think that we should move away from using cpp and move the #ifdef function logic into the main .c files so that they elog ERROR if they are called on a server that doesn't have the necessary library. This would also help my hacked up CMake build system I started working on a while back... 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] (no subject)
On 12/04/11 17:38, Paul Caroline Lewis wrote: Hi, Thank you Mark and Richard for your replies. Having looked at this it seems a Full Vacuum is the answer, however I'm not sure why. Processing the SQL scripts as originall reported I do get a large table from TestSet1 and a small table from TestSet2. Once a Full vacuum is performed on the large table from TestSet1 its size drops to the same as the small table from TestS2, however adding a full vacuum into the TestSet1 procedure makes it slower to run than TestSet2, very much slower especially on uploading the very large data sets (70 mill rows). This begs the question is TestSet2 very efficient or is it missing something fundamental that a Full Vacuum provides that I'm not realising at the moment. That's strange - do you see the same behaviour if you swap the order of the data load, i.e. do the ordered data set first, and/or use a different table name for each load? I'm just wondering if you're seeing some kind of database bloat if VACUUM fixes the issue. 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] (no subject)
On 07/04/11 17:57, Paul Caroline Lewis wrote: DB SIZE PROBLEM: I have run the following tests on a Postgresql 9 with postgis 1.5 platform and am getting significant table and index size differences. TestSet1 is run with a file tunnel6.asc, a CSV file with the following being a sample of the data: -6.34223029,53.39211958,132.586 The file is 6 GB in size with 70 million rows. After running TestSet1 the table has the correct number of rows (70 mill), random viewing of the data and it seems fine, while the table size is 16 GB and the index size is 6195 MB. I now Drop Cascade the tunnel6 table for TestSet2 For TestSet2 it is run on a preprocessed version of tunnel6.asc, now called tunnel6_py.asc, with the following being a sample of the data: -6.34223029,53.39211958,132.586,SRID=4326;POINT(-6.34223029 53.39211958 132.586) This file grows to 8 GB and still has 70 million rows but after following TestSet2 steps while the table still has the correct number of rows (70 mill), random viewing of the data and it still seems fine but now the table size is 9.5 GB and the index size is 3363 MB. Have I done something significantly wrong in these tests? The TestSet2 process loads the data about 10 minutes quicker than TestSet1 so I would like to use it but I don't trust it now given the significant differences in table sizes. Hi Paul, Due to the way in which the GiST indexes work, the order in which data is fed into the index build function can have a noticeable effect on the size and performance of the resulting index. This is because depending upon the order, page splits can occur at different points in the build process and hence influence the overall structure of the index. There has been some talk of optimising the split function to create better indexes, but no-one has really spent much time on it. That said, it appears that whatever you do in your pre-processing does have a noticeable performance benefit so if you can provide us with information as to how this pre-processing affects the order of the data then we may be able to make use of this information. 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] Strange problem building postgis 1.4.2 with geos
On 07/04/11 18:50, Bryan Montgomery wrote: I'm probably missing something simple. However I've been looking at this for a few hours scratching my head. First of, I know it's old versions we're using, I'm hoping to upgrade everything to the latest but We're trying to upgrade a version of geos, so we built and installed the next version of geos. geos-config --version reports 3.1.1. Ok, so far, running configure: PostGIS is now configured for x86_64-unknown-linux-gnu -- Compiler Info - C compiler: gcc -g -O2 C++ compiler: g++ -g -O2 -- Dependencies -- GEOS config: /usr/local/bin/geos-config GEOS version: 3.1 PostgreSQL config:/usr/local/pgsql/bin/pg_config PostgreSQL version: 8.2 PROJ4 version:46 PostGIS debug level: 0 All looks good, so we build ... PostGIS was built successfully. Ready to install. That's strange. Could it be a permissions problem? Does make clean remove postgis/libpostgis-1.4.so? If so, do a configure, make and then make install and check again. 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] postgis 2 installation failing
On 15/03/11 14:07, AJ7 wrote: Hi, I downloaded the source for Postgis2.0, and when trying to install (make) it on snow leopard , its failing and this is the error: collect2: ld returned 1 exit status lipo: can't open input file: /var/folders/Uz/Uz-J-wY0HAW5jjSNhXRDNk+++TI/-Tmp-//ccjtYJzA.out (No such file or directory) make[1]: *** [postgis-2.0.so] Error 1 make: *** [all] Error 1 - The ./configure worked fine. any ideas? thanks That looks strange. I've only seen similar symptoms before when I've run out of memory/disk space when attempting a build. 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 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] make generates incorrect PERL variable value on v1.5.2
On 10/03/11 02:56, Bill Teluk wrote: I used the solution provided in the Wiki page reporting the fault eg. after running configure sed 's,$(PERL),perl,g'postgis/Makefilepostgis/Makefile2 mv postgis/Makefile2 postgis/Makefile ... and it then worked for me. I was wondering if this is actually a bug or is it something about my system that caused this problem? That sounds strange. Can you show us the mangled and un-mangled versions? 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 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] make install as root on v1.5.2
On 10/03/11 02:58, Bill Teluk wrote: Hi, I have installed PostGIS 1.5.2 on a PostgreSQL 9.0.4 (OpenSCG RPM) on an RHEL5.5 system - the instructions in the included README.postgis indicate that I should perform make install as postgres user. This didn't work and generated the error below: /bin/mkdir -p '/opt/postgres/9.0/share/postgresql/contrib/postgis-1.5' /bin/mkdir: cannot create directory `/opt/postgres/9.0/share/postgresql/contrib/postgis-1.5': Permission denied make[1]: *** [installdirs] Error 1 make[1]: Leaving directory `/home/maptools/other/pgis_1_5_2/postgis-1.5.2/postgis' make: *** [postgis-install] Error 2 I overcame this by performing make install as root user - I did this because I noted that the online HTML documentation for v1.5 doesn't indicate which user to install as - at least as far as I could see (ref http://postgis.refractions.net/documentation/manual-1.5/ch02.html#id2630263 ) Is this a documentation error, or should I really be installing as user postgres? Given that you've installed under /opt, I would think that it would be absolutely fine to build and install as the postgres user. It seems to me that this is either a permissions or an SELinux issue (if enabled). HTH, 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 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Infinite loop in st_intersects - because of incorrect data out of st_transform?
On 10/03/11 15:13, Magnus Hagander wrote: What was the discussion with the GEOS people like? Did they consider this to be a bug in GEOS or a bug in PostGIS? Ugh. Re-reading the thread, I realize I've misunderstood this. I thought both issues were in the same way, but I see now once was geos and one was proj. Thus, I expected your cross post in the followup covered it :-) So given that, I haven't actually contacted the geos community specifically - unless they are represented on this list. Having said that, if you (or somebody else who actually knows the PostGIS code well) would handle that contact, that would be great. Given my lack of knowledge in the 10 or so stack frames between my calling and the actual geos call, I fear I won't be able to answer their follow-up questions in a good way :-) I think a basic question of how do we handle NaNs, -Inf/+Inf etc.?, i.e. should GEOS error if it finds them or silently discard them could be answered better on the main GEOS list. Obviously there is some overlap here, but Martin Davis is probably the person with the best feel for how this should work and I don't think he's on any of the PostGIS lists. 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 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Increase query performance
On 08/03/11 17:01, Andreas Forø Tollefsen wrote: After a suggestion from pgsql_performance i tried with ST_Simplify to speed things up. However this gives me a: NOTICE: ptarray_simplify returned a 2 pts array Then server connection terminates. Like this: SELECT ST_Intersection(priogrid_land.cell, ST_Simplify(cshapeswdate.geom,0.1)) AS geom, priogrid_land.gid AS divider, gwcode, gwsyear, gweyear, startdate, enddate, capname, caplong, caplat, col, row, xcoord, ycoord FROM priogrid_land, cshapeswdate WHERE ST_Intersects(priogrid_land.cell, ST_Simplify(cshapeswdate.geom,0.1)) AND cshapeswdate.gwsyear =1946 AND cshapeswdate.gweyear =1946 AND cshapeswdate.startdate = '1946/1/1'; Yup that's a bug related to the new code in trunk - I think we've already got something similar filed in the bug tracker. This is probably going to require a Paul to take a look at it. 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] Infinite loop in st_intersects - because of incorrect data out of st_transform?
On 08/03/11 09:16, Magnus Hagander wrote: Since nobody appears to be too interested in producing a quick fix in geos, attached is a patch that puts in an *ugly* workaround in PostGIS, that simply rejects the infinite values higher up in the stack. I don't consider this a long-term fix, but it at least causes an error instead of getting stuck in an infinite loop that can only be terminated with kill -9... So pending a solution in geos, I would suggest this workaround (or something better located written by someone who actually know the postgis code better than me) be put in. The way it is now, any application that allows the user to specify input that could generate such a geometry could trivially denial-of-service any postgis site... Hi Magnus, What was the discussion with the GEOS people like? Did they consider this to be a bug in GEOS or a bug in PostGIS? The basic workaround looks good, but I'd still be interested on Frank's thoughts re: whether infinity is a valid projection output point in order to determine the best place for the check. 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] insert to database - error
On 02/03/11 07:19, Robert Buckley wrote: Hi, Ubuntu 10.04 geoserver-2.0.2 postgresql 8.4 postgis I am experiencing the following: I can edit a postgis database geometry in Quantum GIS but I can´t insert into a postgis table inserted with shp3pgsql. ( I used the opengeo-suite windows version to upload the shp) my error is here message is here .. http://permalink.gmane.org/gmane.comp.gis.geoserver.user/26390 After days of scratching around the forums and reading this... http://lists.osgeo.org/pipermail/openlayers-users/2010-March/016801.html ..I decided to do the same a make a simple table in postgis - 1 x id column (type - integer, primary key, NotNull) and 1 x the_geom column ( type - geometry) I loaded it into geoserver and was able to start editing immediately Is it then something to do with the constraints inserted by shp2pgsql?..( I removed all of them one by one and tested the insert, but it still didn´t work) Possibly do to with the GID column? I ccan´t remove this column without breaking the geometry structure. Idon´t understand what the problem is with the table imported from a shapefile using shp2pgsql Hi Robert, The error message coming from Geoserver suggests that a prepared statement is failing on the database. Can you try editing your postgresql.conf to enable statement logging and restart PostgreSQL? Then next time Geoserver throws an error, you can look in the log file to see the exact SQL commands that were executed. HTH, 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] speed of query
On 01/03/11 21:48, Michael Smedberg wrote: OK, next guess. It looks like your SQL might be pretty inefficient. For example, consider this SQL statement: SELECT a, b FROM ll WHERE ST_Within( ST_Point( ST_X(ST_Transform(the_geom, 4326)), ST_Y(ST_Transform(the_geom, 4326)) ), ST_MakeBox2D(ST_Point(-91.048, 45.956), ST_Point(-90.973, 46.007)) ) I think that says something like: Look through the II table For each row, transform the_geom to 4326, and take the X For each row, transform the_geom to 4326, and take the Y Make a point from that X,Y Check if that point is within a box I don't think that'll use an index, and I think it will do a bunch of transformation work for every row. I think that instead of transforming every row in II to 4326, you'd probably be better served by transforming your bounding box to 2163 one time. I think the SQL would look something like this: SELECT a, b FROM ll WHERE ST_Within( the_geom, ST_Transform( ST_MakeBox2D( ST_Point( -91.048, 45.956 ), ST_Point( -90.973, 46.007 ) ), 2163 ) ) In any case, you should probably try looking at the output of EXPLAIN or EXPLAIN ANALYZE to understand whether your index is being used, etc. Hi Michael, Yes indeed - I think you summarised this brilliantly :) If your geometries are stored as SRID 2163, then the index bounding boxes will also be stored in SRID 2163 - hence the index can only be used for queries involving other SRID 2163 bounding boxes. Otherwise PostgreSQL assumes it has to convert your entire geometry column to SRID 4326 first in order to calculate the intersection, which involves scanning the entire table and converting all the geometries on the fly... 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] Securing postgis
On 02/03/11 15:17, Karl O. Pinc wrote: Yes, I've been thinking more about this and have come to the same conclusion you have. The right way to go is to put triggers on geometry_columns that check permissions against what's granted on the column holding the geometry data. Any clue if this would be something I could send in as a patch to the postgis project? Probably not, as I believe Paul is planning to switch the binary representation of the geometry type in 2.0 which would then enable us to make geometry_columns a view just like geography_columns. However, it would probably be of interest to enough people to add it somewhere to the user part of the wiki. 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] Securing postgis
On 02/03/11 16:30, Karl O. Pinc wrote: Humm. Ok. I'm unfamiliar with geography_columns. What is the security model? Can anyone do anything like with geography_columns? If it's a view then would it be possible to put triggers on the underlying tables? Where would I look in the code? Thanks. It's still under discussion, but the view is generated automatically by querying the system catalogues to pull out the spatial columns (i.e. it can't be altered, but users will be able to see other users' spatial columns in there). This means that AddGeographyColumn() and DropGeometryColumn() can just add/remove the column from the table directly, so this can only happen on tables for which the current role has SQL permissions. HTH, 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] shp2pgsql
On 16/02/11 14:29, Mehmet Erkek wrote: Hi everyone, Wishing you all a fruitful day, here is a problem we have and we are hoping to get some help: We are converting a shapefile to postgis with shp2pgsql. Conversion is done without problem. However, it produces boundaries with fewer points than we expect, thus it produces sharper boundaries and sometimes overlaps with neighboring boundaries. Is there way to make it produce outputs with more points and much closer to original shapefile? I appreciate any comment on this. Thanks. I think you need to clarify this with an example, since shp2pgsql doesn't alter the input geometries, it merely converts them to binary for storage within the database. 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] [GENERAL] How to improve this query?
On 08/02/11 11:05, Jorge Arévalo wrote: 2011/2/8 Stefan Kellersfkel...@gmail.com: Hash Cond: (TABLE_C.STR_FIELD = TABLE_B.STR_FIELD) tells me that these two longish strings are compared first. Did you already try to index TABLE_C.STR_FIELD and TABLE_B.STR_FIELD as hash instead btree? I thought hash indexes were discouraged over btree. Anyway, I'll test it. Absolutely. You do not want to be using Hash Indexes these days - stick with B-Tree. The hash refers to the method by which the executor matches the two column fields and is not related to the underlying index type. 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] Why use C procedurals for Postgres
On 21/01/11 13:44, Rudy COMMENGE wrote: Hello, I have installed Postgis with PostgreSQL. When I try to send postgis.sql to PostgreSQL, I have an error notifying C is not trusted. So I think I have found a solution : I disable the security with this request UPDATE pg_language SET lanpltrusted=true WHERE lanname='c'; But this is a security, so is there another solution without disable ? If not, why Postgis need to bypass a PostgreSQL security ? Regards, RudyWI Hi Rudy, Only the PostgreSQL super-user can install C functions into the database, and for good reason. A C function can execute any code in the context of the database, and so your change above has opened up a big security hole in your database - I strongly recommend you change it back. Note that once you've installed PostGIS as the database super-user, you can always use ALTER TABLE...OWNER... to change the ownership of your tables back to your normal (non-super) user. HTH, 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] Build PostGIS 1.5.2 under Windows
On 18/01/11 19:39, Ilie, Radu wrote: Hi, We need to build PostGIS 1.5.2 as 32 bit under Windows, using Visual C++. We tried to follow the instructions from http://trac.osgeo.org/postgis/wiki/DevWikiWinVC_15, but the instructions from there are not complete. We tried to follow the logical path from the end of the instructions, but we get thousands of compilation errors. I noticed at http://www.postgis.org/download/windows/experimental.php that there are binaries available for distribution under Windows. I imagine the people building them have a build environment for Windows already set up. Does anybody know the steps followed by the people that build the Windows binaries? Are there any updated instructions available for building PostGIS under Windows? Even if they are cumbersome, we can follow them. Thank you, Sadly MSVC builds under Windows are currently unsupported, however if you do manage to get any further please do update the wiki with your progress. Is there any particular reason that you can't use the MingW builds? 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] Build PostGIS 1.5.2 under Windows
On 19/01/11 13:27, Ilie, Radu wrote: I am not familiar with the MingW builds. If we do use them, will the resulting dll's have any dependencies on any runtime component of MingW? Or can we simply take the resulting dll's (for postgis, geos, proj etc) and copy them to another Windows system that doesn't have any part of MingW installed? Radu You can check yourself with DEPENDS.EXE, but normally the only dependency is the standard MSVCRT.DLL runtime supplied with Windows. Possibly newer versions may have one additional DLL dependency for debug stack handling. HTH, 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] Null value when inserting large linestrings or multilinestrings in windows
On 31/12/10 00:02, Stanley Sufficool wrote: This question has come up sooo many times in so many lists. Why not just have PGAdmin return avalue too large to display or some indicator besides blank to indicate a too large value?? That sounds like a good plan - feel free to raise this on the pgadmin-support list (see http://www.pgadmin.org/development/list.php for more information). 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] import shape or sql to postgres automatically
zhang zhe wrote: Hello, Thanks. Actually, I have windows and apache server installed in my computer. In this case how the code should look like? What´s the mylinuxserver ? is it the IP adress of the server? or name of the server? Will my code changed into psql.exe -h mywindowsServer -d postgres -U zhe -f c:/users/sqlfile.sql You can, but I would highly recommend that you read the psql.exe documentation at http://www.postgresql.org/docs/9.0/interactive/app-psql.html which should answer all your questions. In particular, the postgres database is an in-built database designed for maintenance and shouldn't be used to store user data. Can I write this code to php? Indeed - you'll need to look at the exec() function. Don't forget to escape your shell characters for security. HTH, 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] Kyng chaos Postgis -- PostGIS requires PostgreSQL 9.0.
fork wrote: Hi all, Not sure where else to ask -- I just installed Postgresql 9.0.1 from the Kyngesbury site, now trying to install postgis, and I get this error: PostGIS requires PostgreSQL 9.0. Anyone have any ideas? Does this mean I can't find any postgres? or I can't find the right version or ??? Thanks! There was a bug in PostGIS 1.5.1 that would probably cause this error (related to the change in version number from PostgreSQL 8.5 to 9.0). Can you confirm that you are using PostGIS 1.5.2? 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] import shape or sql to postgres automatically
zhang zhe wrote: Hello, Does anyone know how to import sql table or shape file to postgres database automatically? What I did before is: I have shpe file save in C:/users I go to terminal in linux, and go to the directory C:/users C:/users shp2pgsql shapefile.shp sqlfile.sql I will get sql file saved into c:/users directory. I open the sql file copy all the sql query. After that I open postgres database, and paste all the query to sql query window, and run the query. I will have new table appear in the dabase. Sometimes the sql table is too large, that I cannot paste them all in the postgres sql query window. Postgres is always get stuck because of the query is too large.For instance I have road dataset of whole contry, and it has really millions of queries. Is there any way to import shape file or sql table to Postgres database automatically without any manual work? Assuming you have the PostgreSQL client installed on your local Windows workstation, it sounds as if you need to pass the -f option to psql and then use -h to connect to your linux server instead, e.g: psql.exe -h mylinuxserver -d mydatabase -U myuser -f c:/users/sqlfile.sql HTH, 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] PostGIS training courses @ PGDay EU 2010
Hi everyone, For those of you interested in learning more about PostgreSQL/PostGIS, I'm pleased to announce that I'll be running two training courses as part of PGDay EU (http://2010.pgday.eu/training). The first course is aimed at people new to PostGIS, while the second is aimed at power users with more advanced requirements. Each of these courses costs 150EU and registration in advance is essential. Introduction to PostGIS: an open-source spatial database http://www.postgresql.eu/events/schedule/pgday2010/session/39-introduction-to-postgis-an-open-source-spatial-database-registration-required/ Advanced PostGIS: tips and techniques for power users http://www.postgresql.eu/events/schedule/pgday2010/session/40-advanced-postgis-tips-and-techniques-for-power-users-registration-required/ For those people based in Europe, it's an ideal opportunity not only to learn more in-depth about PostgreSQL/PostGIS but also to meet other users developing their own applications. Looking forward to seeing you in Stuttgart, 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] pgsql2shp dbf file encoding
Denis Rykov wrote: After editing dbf file in hex editor and set value at byte 29 to 00h shapefile opens in ArcGIS without encoding troubles (get codepage value from *.cpg file). That's strange. Does anyone know what the behaviour of the psDBF-iLanguageDriver field should be in terms of how it reacts with a .cpg file? Frank? 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] pgsql2shp - No GID variable
Andreas Forø Tollefsen wrote: Hi all, When using the pgsql2shp the gid column is not included. I even tried typing a select gid AS g_id to include it as a different column name, but this did not work. My syntax: pgsql2shp -f pgfinal2008.shp -h localhost -u user -P pass postgis public.pgfinal2008 SELECT *, gid as g_id FROM pgfinal2008 Any ideas how i manage to extract the gid column from my tables? Hi Andreas, Have you tried looking at the -r option for pgsql2shp? 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] pgsql2shp dbf file encoding
Denis Rykov wrote: Try to export postgis data to shapefiles with pgsql2shp (pgsql2shp-core.h 5870 2010-08-28 09:16:32Z mcayland) If open *.dbf file I see the value in my dbf files at byte 29 is 0x57h. Is the 0x57h value is default? Why not 0x00h? With 0x57h encoding my shapefiles looks not correct in any GIS software. I don't think it's currently set to anything, so I guess this would be the default? Perhaps we should provide a mapping from PostgreSQL database encoding names to shapefile encoding values in a table somewhere? Anyone know which encoding 0x57h represents? 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] pgsql2shp dbf file encoding
Denis Rykov wrote: I don't quite understand why pgsql2shp is writing this encoding to our shapes, our database is in UTF-8 and we never use win1252 Well pgsql2shp has never contained any code to set the encoding field (mainly because until recently the version of shapelib included with PostGIS didn't support the encoding field), so I guess WIN1252 must be the shapelib default. 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] issue while building postgis-1.5.2 with pg-9
Dharmendra Goyal wrote: Hi Mark, I added one CFLAG /GS- and security cookie related warnings went away but following symbols still could not be found: c:/pginstaller.pune-repo/proj-4.7.0.staging/lib/proj.lib(pj_gridinfo.obj):(.text+0x6e7): undefined reference to `_ftol2_sse' c:/pginstaller.pune-repo/proj-4.7.0.staging/lib/proj.lib(pj_gridinfo.obj):(.text+0x6fc): undefined reference to `_ftol2_sse' c:/pginstaller.pune-repo/proj-4.7.0.staging/lib/proj.lib(pj_gridinfo.obj):(.text+0xade): undefined reference to `_ftol2_sse' c:/pginstaller.pune-repo/proj-4.7.0.staging/lib/proj.lib(pj_gridinfo.obj):(.text+0xaf3): undefined reference to `_ftol2_sse' c:/pginstaller.pune-repo/proj-4.7.0.staging/lib/proj.lib(pj_gridlist.obj):(.text+0x1a2): undefined reference to `_imp___wassert' c:/pginstaller.pune-repo/proj-4.7.0.staging/lib/proj.lib(PJ_tmerc.obj):(.text+0x917): undefined reference to `_ftol2_sse' c:/pginstaller.pune-repo/proj-4.7.0.staging/lib/proj.lib(PJ_robin.obj):(.text+0x2e): undefined reference to `_ftol2_sse' c:/pginstaller.pune-repo/proj-4.7.0.staging/lib/proj.lib(PJ_robin.obj):(.text+0x181): undefined reference to `_ftol2_sse' c:/pginstaller.pune-repo/proj-4.7.0.staging/lib/proj.lib(nad_intr.obj):(.text+0x21): undefined reference to `_ftol2_sse' c:/pginstaller.pune-repo/proj-4.7.0.staging/lib/proj.lib(nad_intr.obj):(.text+0x42): undefined reference to `_ftol2_sse' collect2: ld returned 1 exit status This is coming at the time of linking: dllwrap -o postgis-1.5.dll --dllname postgis-1.5.dll --def libpostgis-1.5dll.def lwgeom_pg.o lwgeom_debug.o lwgeom_accum.o lwgeom_spheroid.o lwgeom_ogc.o lwgeom_functions_analytic.o lwgeom_inout.o lwgeom_estimate.o lwgeom_functions_basic.o lwgeom_gist.o lwgeom_btree.o lwgeom_transform.o lwgeom_box.o lwgeom_box3d.o lwgeom_box2dfloat4.o lwgeom_chip.o lwgeom_geos.o lwgeom_geos_prepared.o lwgeom_export.o lwgeom_svg.o lwgeom_gml.o lwgeom_kml.o lwgeom_geojson.o lwgeom_in_gml.o lwgeom_in_kml.o lwgeom_triggers.o lwgeom_dump.o lwgeom_functions_lrs.o long_xact.o lwgeom_sqlmm.o lwgeom_rtree.o geography_inout.o geography_gist.o geography_btree.o geography_estimate.o geography_measurement.o -Lc:/PGINST~1.PUN/PGSQL-~1.1/lib -Wl,--allow-multiple-definition -L/C/pgBuild/krb5/lib/i386 -L/C/pgBuild/OpenSSL/lib -L/C/pgBuild/zlib/lib -Wl,--as-needed -L/C/pginstaller.pune-repo/geos-3.1.1.staging/lib -L/C/pginstaller.pune-repo/proj-4.7.0.staging/lib -lgeos_c -lproj -L/c/pgBuild/libxml2_mingw/lib -lxml2 -lws2_32 ../liblwgeom/liblwgeom.a -Lc:/PGINST~1.PUN/PGSQL-~1.1/lib -lpostgres Any clue...?? Regards, Dharmendra Sorry, I don't think I can help you any more :( All the builds I have done in the past (as well as the current builds) are all done using MingW and they all appear to work fine. I guess it must be related something in your particular build environment. My suspicion would be that its something in the MSVC PROJ.4 project settings somewhere, but you'd need to ask someone who uses MSVC for more help. 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] issue while building postgis-1.5.2 with pg-9
Dharmendra Goyal wrote: Hi, I am trying to build postgis-1.5.2 with postgresql-9.0.1 and i am getting following error while building the sources: c:/pginstaller.pune-repo/proj-4.6.1.staging/lib/proj.lib(pj_utils.obj):(.text+0x47): undefined reference to `__security_cookie' c:/pginstaller.pune-repo/proj-4.6.1.staging/lib/proj.lib(pj_utils.obj):(.text+0x539): undefined reference to `...@__security_check_cookie@4' Error is coming just after executing: make[1]: Entering directory `/c/pginstaller.pune-repo/postgis.windows/postgis' dllwrap -o postgis-1.5.dll --dllname postgis-1.5.dll --def libpostgis-1.5dll.def lwgeom_pg.o lwgeom_debug.o lwgeom_accum.o lwgeom_spheroid.o lwgeom_ogc.o lwgeom_functions_analytic.o lwgeom_inout.o lwgeom_estimate.o lwgeom_functions_basic.o lwgeom_gist.o lwgeom_btree.o lwgeom_transform.o lwgeom_box.o lwgeom_box3d.o lwgeom_box2dfloat4.o lwgeom_chip.o lwgeom_geos.o lwgeom_geos_prepared.o lwgeom_export.o lwgeom_svg.o lwgeom_gml.o lwgeom_kml.o lwgeom_geojson.o lwgeom_in_gml.o lwgeom_in_kml.o lwgeom_triggers.o lwgeom_dump.o lwgeom_functions_lrs.o long_xact.o lwgeom_sqlmm.o lwgeom_rtree.o geography_inout.o geography_gist.o geography_btree.o geography_estimate.o geography_measurement.o -Lc:/PGINST~1.PUN/PGSQL-~1.1/lib -Wl,--allow-multiple-definition -L/C/pgBuild/krb5/lib/i386 -L/C/pgBuild/OpenSSL/lib -L/C/pgBuild/zlib/lib -Wl,--as-needed -L/C/pginstaller.pune-repo/geos-3.1.1.staging/lib -L/C/pginstaller.pune-repo/proj-4.6.1.staging/lib -lgeos_c -lproj -L/c/pgBuild/libxml2_mingw/lib -lxml2 -lws2_32 ../liblwgeom/liblwgeom.a -Lc:/PGINST~1.PUN/PGSQL-~1.1/lib -lpostgres Looks like an MSVC-specific issue with the PROJ.4 library to me: http://blogs.msdn.com/b/nikolad/archive/2005/01/27/unresolved-external-security-cookie-with-platform-sdk-compiler.aspx 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] shp2pgsql 1.5 is crashing
Stephen Woodbridge wrote: Hi all, I'm running into a crashing problem with shp2pgsql on 1.5 release on a CentOS 5 This crashes: shp2pgsql -s 4326 -c -D Hydrants_4326.shp data.hydrants This works ok: shp2pgsql -s 4326 -c -D -N skip Hydrants_4326.shp data.hydrants File loads ok in OpenEV and runs through ogr2ogr without a problem. -Steve W Hi Steve, If you're not already running 1.5.2, I'd try that as I seem to remember there were a couple of bugfixes that might be relevant here. HTH, 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] query gist bbox
Till Kirchner wrote: Hello together, I would like to visualize and query the bboxes used by a gist (rtree) index. I thought there should be an easy and fast way to get the maximum extend of geometries stored in a column by simply querying the corners of the bbox which covers all data used by the index. Is there a function which allows to get information about bboxes used by a gist index? Thanks in advance Till Hi Till, Take a look at the gevel module here: http://www.sai.msu.su/~megera/postgres/gist/. I did have a very basic prototype which I posted to the -users list a few years ago, but it needs some work as the visibility of the bounding boxes (i.e. whether they belong to geometry that has been deleted or not) is held in the heap and not the index. Unfortunately I don't have the URL to hand at the moment. HTH, 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] refractions web site
Guillaume Sueur wrote: Hi, What's wrong with www.refractions.net website and its sub-domains like postgis.refractions.net ? Has it moved somewhere else ? Thanks Guillaume You can still access the PostGIS site at http://www.postgis.org. The Refractions guys are aware of the issue and I'm sure will post an update when everything is back to normal. 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] pgsql2shp revision
strk wrote: Perhaps we could touch the pgsql2shp-core.h file but that just seems messy :( Why drop the feature ? If we want a true repository revision we should provide that in an header file (which we'd touch) and use that when we see fit. I wouldn't drop the file-specific one though, you can always get a diff from current to the specific one w/out querying the server (right? or time to move to git :). Yeah; out of all the options I'd prefer to keep the standard svn:keywords tag and touch the file when appropriate (maybe as part of the release process?). It's not a great solution, but probably the least worse out of all the alternatives. 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: I've try to get exit code with my python script: import os code = os.system(pgsql2shp -f dagestan.shp -h gis-lab.info http://gis-lab.info/ -u labinfo -P *** osm \SELECT ^^ st_intersection as geom from dagestan_point_attr WHERE highway='bus_stop') print code At result I've got 256 (not 2 as expected). H looks like you're missing an escaped quote somewhere? If your svn up worked, the exit code should be either 0 or 1. To test from bash: pgsql2shp -f dagestan.shp SELECT st_intersection as geom from dagestan_point_attr WHERE highway='bus_stop' echo $? HTH, 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] pgsql2shp revision
Denis Rykov wrote: Is the pgsl2shp show correct SVN version of revision? After updated pgsql2shp from svn and start pgsql2shp without options I get: RCSID: $Id: pgsql2shp-core.h 5870 2010-08-28 09:16:32Z mcayland $ RELEASE: 2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 But last SVN revision number is not 5870. This appears to be a feature of SVN in that the revision number embedded in the file is the last revision that altered that particular file, and not the latest revision of the whole repository. Since we store the revision information in a separate file (because it is re-used in more than one place) then you don't see the true repository revision. Perhaps we could touch the pgsql2shp-core.h file but that just seems messy :( 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: 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] 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
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] 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
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
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
Re: [postgis-users] pgsql2shp fails
Denis Rykov wrote: Result of \d public.results in psql: Table public.results Column | Type | Modifiers --+---+--- the_geom | geometry | reg | character(16) | Thanks for the bug report - should be fixed in latest SVN. Can you do an svn up and confirm that it now works for you? Many thanks, 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] pgsql2shp fails
Denis Rykov wrote: Hello, Regina. There is information about my system: 1) RCSID: $Id: pgsql2shp-core.h 5646 2010-05-27 13:19:12Z pramsey $ RELEASE: 2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 2) pgsql2shp -f $dir_output/bounds/bnd-a-osm-buf.shp -U pgsql -d osm public.results 3) PostgreSQL 8.4 4) 2.0 r5830 5) FreeBSD 8.1 I get the following error: Error executing main scan query: ERROR: column gid does not exist LINE 1: ... -1), 'NDR') AS _geoX FROM public.results ORDER BY gid That's probably my bug (although the other apparent bug is that I forgot to set the svn id property on pgsql2shp-core.h too). What does the output of \d public.results look like in psql? 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] Shape file importer. broken multipolygon
Nicolas Gillet - MARKET-IP wrote: Hello List, I used the PGAdmin III plug-in to import shape files into my database but I’m facing a problem. The shape file I am importing contains multi-polygon but the resulting table after import contains only single polygon and some polygons are then lost. Only one polygon is imported for every multi-polygon in the file. If I use the shp2pqsql command line tool (instead of GUI), I have the right result. My multi-polygons are as they’re supposed to be. So I think that the import plug-in is somehow configured to use something like the “-S” forced switch of the shp2pqsql tool but I don’t find such option in the plug-in’s options. I use a PosgreSql 8.4, Postgis 1.5 with PgAdmin III. The import plugin version is : shp2pgsql-gui $Revision: 5094 $. Any idea of what’s wrong ? Thanks for any input. Nicolas Hi Nicolas, It looks as if this bug has already been fixed in r5195, and hence should be included from PostGIS 1.5.0 onwards. Where did you get your PostGIS binaries from? HTH, 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] Update to current shapelib
Peter Hopfgartner wrote: Hi I tried to upgrade the shapelib version in PostGIS to the current one, as found in the current shapelib CVS repository. What I did was: * copy safileio.c shapefil.h shpopen.c dbfopen.c from shapelib * add support for dates and include the functions DBFReadSetup and DBFReadDeleted from the current PostGIS shapelib version. * adapt Makefile.in This was done with 1.5, adapting this to trunk should be easy. Regards, Peter Hi Peter, Thanks for this. Was this for a particular bug that you needed to work around? Please feel free to create a ticket in the bugtracker and attach a patch in diff -u format against SVN trunk. 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] Help in Defending FOSS SDI
Ravi wrote: Some so called SDI experts feel that FOSS SDI cannot perform at-par with Proprietary SDI. Please provide examples to fight a case from an Indian state which swears by Free and Open Source Software. We can never expect a better level playing field. Kerala - India Here are some excerpts from a document that has false claims supporting Proprietary Software. However, it is worthwhile to mention here that the OSS (Open Source Software) does not match the advanced functionalities of many of the commercial (proprietory) software that is in the market. Image processing and analysis capabilities of the open source software is not comparable to the commercial software when one require to carry out advanced data manipulations, image fusion, 3D modeling, ortho-correction, auto-georeferencing, stereo-image/air photo interpretation (PROBABLY REFERRING TO GRASS), advanced geospatial analysis etc., In such cases, certain proprietary software become an integral part of the Spatial Data Infrastructures, which can not be avoided. At a later stage the some of the proprietary software need to be purchased. The issue I see with this paragraph is that it is merely handwaving and FUD; I don't see any specifics given. In particular, I would ask for the specific processes and workflows they are referring to (in particular which software/plugins they are using) so that you can point them towards an equivalent OSS solution. It is a well known fact that web portal that run with OSS are neither OGC-compliant nor interoperable(PostGIS and Webservers to react). At the present juncture it is only possible to establish the KSDI Geoportal with the available COTS enterprise software. This is just completely wrong; PostGIS is based upon the OGC SFS for storing geometries and we have worked hard to ensure that it meets the standard in all regards. In terms of other applications, servers like Mapserver and Geoserver speak standards-compliant WMS/WFS/KML so again I am amazed that they are making this claim. If they are looking for ex-commercial software, there is always Autodesk's open-sourced MapGuide application. Or, if they really do want to use proprietary software in the stack somewhere, then ArcGIS Server supports PostGIS from version 9.3. This is a case that has the potential to set trends in India. Hope to have a good discussion such that we can sum it up and present at a meeting being conducted on August 11th 2010, to settle the issue. If the above sample is representative of the document, then you need to do a lot of research into debunking these false claims and the FUD around them. The excerpts you have provided are so full of lies that I can't help but feel there is a clash of commercial interest somewhere in this project. 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] FME and Postgis-SVN problems
Andreas Neumann wrote: Hi all, I recently installed Postgis-SVN (1.5.2SVN) together with PostgreSQL 9.0 beta 3 and noticed that FME (from Safe software) cannot read the geometries anymore. It used to work fine with older versions. The error message I get reads as follows: Error in the OGR while importing well known binary geometry Invalid WKB encountered I wonder if other users of Postgis SVN and FME are getting similar problems? Hi Andreas, If you're using PostgreSQL 9.0, you're probably getting affected by this: http://trac.osgeo.org/postgis/ticket/538. In short, it seems that various applications don't use the official PostgreSQL libpq unescape functions but instead implement their own - and since the default representation has changed, the parsing code needs to be updated. Note that you can change the default using the bytea_output GUC in postgresql.conf if updating the software proves to be impossible. HTH, 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] may have postgis store limits for a big project?
Sebastian E. Ovide wrote: Hi Mark, what hardware would you use to manage tables of 40M rows ? (simple queries without Joins !) Each query must response in under 0.1 sec. thanks Hi Sebastian, In terms of CPU power, any modern multi-core processor will perform absolutely fine. Make sure that you have plenty of RAM for caching (more than 4Gb) with nice fast disks to boot. As long as the majority of your queries don't return more than a few hundred rows then I think you will be fine. HTH, 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] Index on the Geometry Column and GEOMETRYCOLLECTION EMPTY
Sorin Dudui wrote: Hello Mark, I am wondering if you had time to look in the issue. Thank you, Sorin Hi Sorin, Unfortunately I've was away working with a client last week. I did have a quick look at your example, but as yet haven't had a spare moment to sit and work through it in detail. 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] may have postgis store limits for a big project?
salas wrote: Hello to all: I am working in a project where they are managed more than 20 geoespatials thematics. Each thematic one has a considerable volume of since information it is of the whole country (I am speaking of a lot more than a million of records). In the project we need to make (mostly) intersection consultations keeping in mind literal attributes. I need to know the experience of somebody in a project of this span and if PostGIS would present some limitation therewith. regards yuriesky Since PostGIS makes use of the underlying PostgreSQL database, then you may find the following table useful: http://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table.2C_and_a_database.3F For several projects I've been involved in, we've had databases into the 10s of millions of rows and PostgreSQL/PostGIS has handled it fine without breaking a sweat :) HTH, 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] shp2pgsql
Bob Pawley wrote: Hi I just attempted to use shp2pgsql and received an error message about libiconv2.dll not being installed on my computer. Can anyone suggest where a clean copy can be found and instructions on how to install it?? Bob Hi Bob, It's included with the PostGIS windows installer, and can normally be found in the PostgreSQL bin directory once the installer has been run. HTH, 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] Index on the Geometry Column and GEOMETRYCOLLECTION EMPTY
Sorin Dudui wrote: Hello Mark, Thank you for your answer. I have sent you at mark.cave-ayl...@siriusit.co.uk some data to reproduce the issue. I am wondering if you have received it. Best regards, Sorin Hi Sorin, Yes, I have received your email containing the test data. I shall attempt to look at this over the next few days. Many thanks, 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] shp2pgsql date quirk
Brian Hamlin wrote: I am importing several large shape files, into pg 8.4 postgis 1.5+ most went in fine, but the last one failed with an error regarding date fields. At least one date field entry in the shape file apparently is 0 pg complains, the conversion fails.. Maybe you need different date settings is suggested.. the database I am importing into already has 60+ tables.. I dont want to change settings on that database to accommodate.. suggestions on an efficient work-around? thanks -Brian Hi Brian, Could you provide us with a shapefile/dbf file containing a few of the entries in question? 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] Instructions on Installing Postgis on Solaris
Chen, Li [Contractor] wrote: So I am sending the following instructions to share experience with others who also want to install postgis on solaris. The main steps are the same as listed in the postgis manual, but I added some details where I think appropriate. Thank you! Would you like to create a Solaris page on the PostGIS wiki containing this information? 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] PostGIS: make check works, but I can't manually create a spatial database
samclemmens wrote: Sorry; I inadvertently posted my previous response twice... The line is: psql:postgis/postgis.sql:80: ERROR: type spheroid already exists So, does this mean that my spatial database was successfully created the first time around despite the notices that the spatial functions (e.g., st_max_distance) do not exist? Older versions of 1.5 tried to remove some legacy functions from the database as part of the installation which is why some people would see error messages at the end of the installation. Since all the SQL functions are now in a transaction, then if you can call any PostGIS function then the installation has completed correctly. Does SELECT postgis_full_version() work for you? If so, you should be fine. HTH, 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] PostGIS: make check works, but I can't manually create a spatial database
samclemmens wrote: Unfortunately, I get the following: postgres=# SELECT postgis_full_version(); ERROR: function postgis_full_version() does not exist LINE 1: SELECT postgis_full_version(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. Okay so something has gone wrong somewhere. Firstly, after make check, did you run make install? Secondly, what happens if you load the postgis.sql file into a brand new database? 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] PostGIS: make check works, but I can't manually create a spatial database
samclemmens wrote: I ran make install after make check, and received the output below. There was no confirmation that it installed properly or not. I tried creating another spatial database using the same commands, but to no avail. ---Same issue. The make install output looks good. Are you sure you get *exactly* the same error as before when installing into a fresh database? The part of the log we need to see when installed into a fresh database are the set of lines just before and just after the first ERROR appears. Please drop the database and try again posting the relevant output. The only other thing I can think of is that someone has installed another version of PostGIS into the template1 database so that it gets automatically added to any new database. HTH, 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] PostGIS: make check works, but I can't manually create a spatial database
samclemmens wrote: I dropped the db and recreated it...and this time it worked. I get the following, which, I'm assuming, means I'm good to go...? testdb1=# SELECT postgis_full_version(); postgis_full_version --- POSTGIS=1.5.0SVN GEOS=3.2.0-CAPI-1.6.0 PROJ=Rel. 4.6.1, 21 August 2008 LIBXML=2.7.6 USE_STATS (1 row) Yep; that looks good to me :) 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] No index usage on geography query plan?
Paragon Corporation wrote:
Mark,
He did include it in an earlier email
wastac= explain analyze select count(*) from wastac.t_tile_geometry where
ST_Intersects(border, ST_GeographyFromText('SRID=4326;POLYGON((116.751709
-31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143
-31.316101,116.751709 -31.381779))'));
Aggregate (cost=364056.80..364056.81 rows=1 width=0) (actual
time=8854.932..8854.933 rows=1 loops=1)
- Seq Scan on t_tile_geometry (cost=0.00..362993.14 rows=425463
width=0) (actual time=2017.629..8854.879 rows=95 loops=1)
Filter: st_intersects(border,
'010320E61001000500F8C610001C305D40B24CBF44BC613FC0D6E253008
C385D40DE72F563935640C0DF1B430070AF5C40EE77280A
F46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::g
eography)
Total runtime: 8854.976 ms
(4 rows)
Ooops - looks like I missed the query switch somewhere in the thread ;)
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] No index usage on geography query plan?
Paragon Corporation wrote:
Nick,
Okay we are seeing the same issue with our fastfoods data even with
smaller windows. I think the clue is the plan here.
The ST_Intersects geography function seems to be treated as a
blackbox rather than a transparent function composed of and _ST_Distance
The magic of the geometry ST_Intersects is that the query plan can see
into the function and decomposes it into
+ _ST_Intersects
Right. I'd quite like to see the explain analyze plan generated when
performing the ST_Intersects() substitution manually, e.g.
explain analyze select count(*) from wastac.t_tile_geometry where
border ST_GeographyFromText('SRID=4326;POLYGON((116.751709
-31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143
-31.316101,116.751709 -31.381779))') AND _ST_Distance(border,
ST_GeographyFromText('SRID=4326;POLYGON((116.751709
-31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143
-31.316101,116.751709 -31.381779))'), 0.0, false) 0.1;
This should give us a better idea as to why this particular plan is
being chosen.
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: could not load library /usr/local/pgsql/lib/postgis-1.5.so
Paul Ramsey wrote: Sounds like you have an older version of GEOS floating around somewhere. find / -name libgeos_c.* P. Yeah, that looks about right from Kai's earlier email: linux-gate.so.1 = (0xe000) libgeos_c.so.1 = /usr/lib/libgeos_c.so.1 (0xb7ea6000) libproj.so.0 = /usr/local/lib/libproj.so.0 (0xb7e73000) libxml2.so.2 = /usr/lib/libxml2.so.2 (0xb7d59000) libc.so.6 = /lib/tls/i686/cmov/libc.so.6 (0xb7c28000) libgeos.so.2 = /usr/lib/libgeos.so.2 (0xb7b27000) libstdc++.so.6 = /usr/lib/libstdc++.so.6 (0xb7a42000) libm.so.6 = /lib/tls/i686/cmov/libm.so.6 (0xb7a1c000) libgcc_s.so.1 = /lib/libgcc_s.so.1 (0xb7a11000) libdl.so.2 = /lib/tls/i686/cmov/libdl.so.2 (0xb7a0d000) libz.so.1 = /usr/lib/libz.so.1 (0xb79f9000) /lib/ld-linux.so.2 (0x8000) Looks as if there is a GEOS v2 already installed (probably installed via a package manager) causing problems. HTH, 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] No index usage on geography query plan?
Nicholas Bower wrote:
But simply swapping the query region above from geometry to geography
we're back to no index usage,
explain analyze select count(*) from wastac.t_tile_geometry_old where
ST_Intersects(border,
ST_GeographyFromText('SRID=4316;POLYGON((116.751709
-31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143
-31.316101,116.751709 -31.381779))'));
Aggregate (cost=362164.77..362164.78 rows=1 width=0) (actual
time=80302.237..80302.237 rows=1 loops=1) - Seq Scan on
t_tile_geometry_old (cost=0.00..361101.11 rows=425463 width=0) (actual
time=19680.252..80302.172 rows=95 loo
ps=1)
Filter: st_intersects((border)::geography,
'010320DC1001000500F8C610001C305D40B24CBF44BC613FC0D6E253008
C385D40DE72F563935640C0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geogra
phy)
Total runtime: 80302.285 ms
Right. So the give away in the above query is the cast in the
st_intersects() filter; it looks as if your border column in
wastac.t_tile_geometry_old is a *geometry* while your intersection
geometry is a *geography*. Since PostgreSQL sees that you are trying to
perform an operation on two different types, it cannot make use of the
indexes on those two columns. Hence it performs a sequential scan across
the whole table converting on the fly, which is going to take a lot more
time.
Try making a copy of your wastac.t_tile_geometry_old table but with a
geography instead of geometry column for border, and you should see an
improvement.
HTH,
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] implement your own index
Biddy wrote: María, thank you for the fast reply. However, these docs refer to the usage of GIST. If I understand it correctly, an R-tree is implemented on top of GIST. What if I don't want to use an R-tree? If I wanted to implement my own ...let's say I want to implement a quadtree or some other new, fancy index. How would I go about doing it? The GIST API currently does not support space partition-based nor ordered indices (see http://www.postgresql.org/docs/8.4/interactive/gist-implementation.html for the exact details). So if you wanted to implement a quadtree or nearest neighbour type index searches then you'd need to alter the GIST API or invent another index AM in PostgreSQL first. You may find the SP-GIST project interesting in this respect: http://www.cs.purdue.edu/spgist/. Additionally, is there a way to change the attributes with which the R-tree is built? Or at least see how the R-tree is built? It depends what attributes you are trying to modify. If they can be defined in terms of the GIST picksplit function then it is likely possible. In terms of visualising the R-Tree itself, Oleg and Teodor's gevel module from http://www.sai.msu.su/~megera/postgres/gist/ can be used as an aid to generate output suitable for rendering. HTH, 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] Bug or Problem with st_transform
Stephen Woodbridge wrote: Hi guys, This is looking like it might be a bug on version POSTGIS=1.3.3 GEOS=3.0.0-CAPI-1.4.1 PROJ=Rel. 4.6.0, 21 Dec 2007 USE_STATS which I know is ancient, but upgrading is not trivial. What I am trying to do is construct a shape in 900913 in meters and then rotate, transform it to 4326 and translate it to a location. The problem is the transform does not look correct. To just rough out the math, 4030 meters should be about 0.036202 degrees based on 4030/40075016*360 = 0.036202 but st_transform is giving back numbers like -5.67596869587729e-09 Is this a known problem on this version? Is my logic faulty? Is there a better way to do this? Thanks, -Steve Hi Steve, The first thing I would suggest is that you run the same query on a spare fresh 1.4/1.5 installation and see what happens. The transformation code had a good tidy up and some logic corrections within that timeframe, including better reporting of error messages. HTH, 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] Problem with probe_geometry_columns()
Fred Lehodey wrote:
Hi,
I have no success trying the function probe_geometry_columns() with
Postgis 1.5.0
1) Not sure but comparing the SQL with Postgis 1.3.3:
the clause (in the INSERT step and not the count of probed)
sridcheck.consrc LIKE '(*srid*('||a.attname||') = %)' in postgis 1.3.3
is now:
sridcheck.consrc LIKE '(*st_srid*('||a.attname||') = %)' in postgis 1.5.0
This looks like a tipo error. (this is not the function here but the
constraint text in pg_constraint)
No, this is correct. The non ST_ prefix functions have been deprecated
since PostGIS 1.2-ish. Do you actually have an ST_srid() function in
your database, e.g. does the following work?
select st_srid(st_geomfromtext('POINT(0 50)', 4326));
Also, did you do a fresh install of 1.5 or did you run the upgrade
script on an existing 1.3 install?
2) I have a second problem with pg_constraint table and the consrc field.
Most of time I have something like :
(public.srid(the_geom) = 27492)
and not (as expected by the function probe_geometry_columns()) :
(srid(the_geom) = 27492)
Thanks for any feed-back.
H. Do you have multiple schemas in your database/PostGIS
installation? Or have you tried to install PostGIS into a specific schema?
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] PostGIS 1.5.1 causing postmaster crash on index creation
Paul Ramsey wrote: Nicholas, Could you confirm that the crash is building an index on a *geography* column, not a *geometry* column? Ie, the problem has not been introduced to old geometry code, it's something in the new geography code. After that I'm going to need the data that causes the error... Paul If it's some kind of memory clobbering bug, then perhaps changing endian will make a difference. Has anyone tried this on a large endian architecture? 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] Nested loop join = very bad performance
Mike Leahy wrote: Mark/List, I just replaced my postgresql.conf with the default copy that appears in /etc/postgresql/8.4/main/ after a fresh install. The performance is pretty much the same as before (maybe even about 400 ms worse than before). Is there anything else I should try? Mike Hi Mike, Which parameters did you change? effective_cache_size and shared_buffers should be tweaked to suit the RAM available in your machine but the rest of the defaults are fairly sensible. You probably want to set effective_cache_size to ~75% of your physical RAM and shared_buffers to ~25%. Does that make any difference at all? Otherwise, you'll need to start breaking down your query into parts to see which bit is causing the slowdown. Start with the innermost query and then add one join at a time until you find the part which is causing the slowdown. 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] Nested loop join = very bad performance
Mike Leahy wrote: Is this something that should be looked into, or should I just incorporate the workaround of disabling nested loop joins into my code? I find that generally PostgreSQL is fairly good at its estimates unless someone has already tried to tune the database. Have you changed any of the settings in postgresql.conf from their defaults? 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] shp2pgsql in quite mode????...(shp2pgsql -q)???
Shreerang Patwardhan wrote: Hey all, I do not wish to display the output of the command shp2pgsql at the terminal. I want the command to run in quite mode. But there is no -q option for shp2pgsql. How do I get around this issue? Perhaps something like: shp2pgsql ... /dev/null or shp2pgsql ... 21 /dev/null HTH, 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] Make install failure in PostGIS-1.5.1
Sachin Srivastava wrote: Hi all, I am building postgis-1.5.1 with (proj 4.7.0, geos-3.2.0 and pg9.0alpha4) on linux (centos5 32 bit). make is successful, however in make install i am getting this error: /bin/sh /home/sachin/pgsql/lib/postgresql/pgxs/src/makefiles/../../config/install-sh -c -d '/home/sachin/pgsql/lib/postgresql' '/home/sachin/pgsql/share/postgresql/contrib/postgis-1.5' make[1]: /home/sachin/pgsql/share/postgresql/contrib/postgis-1.5: No such file or directory make[1]: *** [installdirs] Error 1 make: *** [postgis-install] Error 2 Anything i am missing? Hi Sachin, No, it's a bug :( The PGXSOVERRIDE code to support versioned directory installation under older versions of PostgreSQL had not been updated to check for version 9.0 rather than 8.5. I've fixed this in SVN for both 1.5 branch and trunk, but you should be able to apply the attached patch and then re-run autoconf to generate a new configure file that should work correctly. HTH, 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 Index: configure.ac === --- configure.ac (revision 5420) +++ configure.ac (working copy) @@ -329,10 +329,10 @@ fi fi -dnl Temporary hack until minimum PostgreSQL version is 8.5: -dnl If PostgreSQL 8.5 is detected, trigger the inclusion of the new versioned PGXS targets +dnl Temporary hack until minimum PostgreSQL version is 9.0: +dnl If PostgreSQL 9.0 is detected, trigger the inclusion of the new versioned PGXS targets PGXSOVERRIDE=0 -if test ! $PGSQL_MINOR_VERSION -ge 5; then +if test ! $PGSQL_MAJOR_VERSION -ge 9; then PGXSOVERRIDE=1 fi ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Missing postgis_config.h
Dan Ramage wrote: I’m trying to make the latest 1.5.0 as well as the 1.4.1 release, however I am getting an error of: “liblwgeom.h:18:31: error: ../postgis_config.h: No such file or directory” I see in the archives this was an issue with a previous version and the suggestion was to pull from the archive. Should I do the same as well, or might there be another issue? Hi Dan, postgis_config.h is generated by running configure. What does the output of the configure command look like? 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] ESRI Shape to Postgres
Khurram Niaz Sheikh wrote:
[CC-d to postgis-users list]
Hi Mark,
Can u plz help me on this, I have just installed to evaluate postgres, I
have the data in shape format and wish to convert to postgres format,
when I try to do it using shp to postgres importer it gives me error
Connecting: user=postgres password=** port=5432 host=localhost
dbname=postgres
Connection succeeded.
Connection: user=postgres password=** port=5432 host=localhost
dbname=postgres
Destination: public.postgrestest
Source File: D:\My Documents\Data\postgres\postgrestest
Shapefile type: Arc
Postgis type: MULTILINESTRING[2]
Failed SQL begins: SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
CREATE TABLE public.postgrestest (gid serial PRIMARY KEY,
road_id int4,
priority int2,
name varchar(250),
geography varchar(50));
SELECT AddGeometryColumn('public','po
Failed in pgui_exec(): ERROR: function addgeometrycolumn(unknown,
unknown, unknown, unknown, unknown, integer) does not exist
LINE 9: SELECT AddGeometryColumn('public','postgrestest','the_geom',...
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
Shapefile import failed.
Can uplz give me solution of this issue.
Hi Khurram,
Please email the list directly in future since it is much more likely
that someone will be able to help you.
Note that the in-built postgres database is for
maintenance/administration only - you need to create yourself a new
database and load postgis.sql into it, as per the manual here:
http://postgis.refractions.net/documentation/manual-1.5/ch02.html#id2797624.
HTH,
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] postgis upgrade 1.4 to 1.5 sql file couldn't find $libdir/postgis-1.4
Bruno Friedmann wrote: Doesn't know if already reported or what I'm doing wrong. In rpm (suppose to be the same with .deb) if you upgrade postgis from 1.4 to 1.5 the postgis-1.4 referenced in function doesn't exist after upgrade. As the default postgis upgrade script call the select postgis_lib_version(); we get a fatal error ERROR: could not access file $libdir/postgis-1.4: No such file or directory and all the process fail leaving normal user in trouble ... I've try to bypass it commenting the SELECT postgis_major_version_check(); and get the sql executed ... Any comments ? Hi Bruno, Sorry but I'm not really sure what's happening here - I know that people have tested the upgrade script when upgrading from 1.4 to 1.5 before the final release and reported that it works. I suspect you will need to talk to the maintainer of the .deb packages you are using to find out why this is failing. HTH, 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] ESRI Shape to Postgres
Khurram Niaz Sheikh wrote: Thanks Mark, Can anyone plz me on this, I have tried to create a new database as well but still get the same error. Regards, Khuram Hi Khurram, You'll have to show us all of the commands you typed from creating the database to loading the shapefile in order for us to help 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] ESRI Shape to Postgres
Khurram Niaz Sheikh wrote: Hi, I created the database using admin console, I mean rigtclick and create a new database, do I have to create the database using command line, plz advise? Regards, Khuram Hi Khuram, You need to load the postgis.sql and spatial_ref_sys.sql files into your database in order to spatially enable it, as per http://postgis.refractions.net/documentation/manual-1.5/ch02.html#id2797624. HTH, 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] [postgis-devel] Has anyone seen this before?
Paragon Corporation wrote: It should be noted that for this table the_geom is the first field in the table and addr_num_tlid comes right after and is a varchar I can select any integer or bigint field fine, but selecting any text or varchar field results in the ERROR: invalid memory alloc request size 18446744073709551613 Yup, this is totally wrong. I suspect bad RAM and/or disk on the machine in question. 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] Postgis on AIX
Derek Jones wrote: Hi folks, I have a need to run postgis on AIX. (lots cut) It doesn't appear to complete the postgis shared lib load. Compared with the same o/p from strace on x86, where it goes on to load up geos and then proj just fine. The only time I've seen the postgis-1.4.so module fail to load on startup is when PostgreSQL has been unable to locate the GEOS and/or PROJ.4 libraries. The first thing I would check is that if you've installed everything with --prefix=/usr1/local to ensure that /usr1/local/lib is in the currently library search path. I know you can set this in Linux with ldconfig and Solaris with crle, but I'm not sure about the AIX equivalent. Once this is set, you should then find that restarting PostgreSQL will then allow the PostGIS module to load. Any thoughts appreciated. Stuck with an 18CPU partition and 64G of memory and can't use it until I fix this :-) Nice :) HTH, 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
