[postgis-users] Proper way to upgrade after using CREATE EXTENSION postgis?
Hello all, I installed PostGIS 2.0 and added it to a database using: CREATE EXTENSION postgis; Today, I built and installed 2.0.1 and tried to upgrade the extension using: ALTER EXTENSION postgis UPDATE; However, this failed with: ERROR: cannot drop cast from geometry to geometry because extension postgis requires it HINT: You can drop extension postgis instead. So, I tried the old school way of running the upgrade scripts: psql -f /usr/local/share/postgis/postgis_upgrade_20_minor.sql hum_bay_gis But this also fails: BEGIN CREATE FUNCTION postgis_major_version_check -- Scripts versions checked for upgrade: ok (1 row) DROP FUNCTION psql:/usr/local/share/postgis/postgis_upgrade_20_minor.sql:46: ERROR: cannot drop function addgeometrycolumn(character varying,character varying,character varying,character varying,integer,character varying,integer,boolean) because extension postgis requires it HINT: You can drop extension postgis instead. psql:/usr/local/share/postgis/postgis_upgrade_20_minor.sql:47: ERROR: current transaction is aborted, commands ignored until end of transaction block ...(thousands of similar errors)... ROLLBACK So, it appears there is no way to upgrade PostGIS without running `DROP EXTENSION postgis` which would cascade to all spatially-enabled tables. Am I missing a step or is there a circular dependency here that is breaking the upgrade process? -Charlie ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Proper way to upgrade after using CREATE EXTENSION postgis?
On Monday, June 25, 2012 12:33:06 PM UTC-8, Paragon Corporation wrote: It worked fine for me upgrading from 2.0.0 to 2.0.1. I used: ALTER EXTENSION postgis UPDATE TO 2.0.1; Which version of PostGIS are you using. As stated in the first email, I was moving from version 2.0 to 2.0.1. However, I just re-installed 2.0 to get the output of postgis_full_version(). Along the way I updated PostgreSQL from 9.1.3 to 9.1.4 and then re-tried the 2.0.1 upgrade. This time ALTER EXTENSION worked without complaint. Perhaps there was a bug in Postgres that got fixed by the 9.1.4 upgrade. -Charlie ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Loading a shapefile into a postgres database remotely
On Sunday, June 3, 2012 10:05:45 AM UTC-7, Stephen Woodbridge wrote: The server has to be configured to listen on 75.75.75.75 and to allow connection over the network device. Look at your pg_hba.conf and postgresql.conf files. and if you make changes to these you have to restart the server before the changes take effect. -Steve Another trick that I use all the time is to tunnel the SQL connection from the remote server to my machine: ssh -L5432:localhost:5432 user name@remote server address Then I can connect to the remote database as if it was running locally and there is no need to mess with the pg_hba.conf file and figure out how to secure the open port, route communication through firewalls, etc. -Charlie ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Loading a shapefile into a postgres database remotely
On Sunday, June 3, 2012 10:22:03 AM UTC-7, David Quinn wrote: I have already edited the pg_hba.conf and postgresql.conf files and can connect remotely using pgadmin so I think that is not a problem. Charlie, should I include this code in the command that I described earlier, or is it something that I should run first? It is something that you should run first, in a separate terminal. Note that since you are using Windows you may not have an `ssh` command unless you have installed a suite of UNIX tools such as MSYS. You may also be able to set up a SSH tunnel using PuTTY. -Charlie ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS through xCode
On Friday, June 1, 2012 4:30:41 AM UTC-7, chris mollis wrote: Right.. using REST calls is the way to do that. You'll basically need to do any complex geo queries/functions in PostGIS and then return vectors of lon/lat points which you then display with the MapKit apis. On Fri, Jun 1, 2012 at 7:24 AM, Charles Galpin wrote: Hi Ahmad You won't connect to the database directly. Depending on what the data is you can use a variety of methods. Simple REST style calls to a web server which runs code to query the database and return the data could be sufficient, but for anything more complex I suggest using a WMS server like geoserver or mapserver. If you want your app to run on multiple platforms consider doing it in html 5. hth charles CartoDB is also a good choice that is worth some investigation: http://cartodb.com/ They do a great job of wrapping a web-accessible API around PostGIS tables. The source code for the entire system is also freely available on GitHub---but it does take a bit of work to unbox, configure and maintain by yourself. -Charlie ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Adding geometry to an existing table
On Saturday, April 28, 2012 12:03:09 PM UTC-7, pcr...@pcreso.com wrote: Hi Shaun, You add a geometry column to the table, then run an update sql to populate it. eg, assuming your table is called mytable, located in the public schema, you want to create a two dimensional point with lat/long coordinates (EPSG:4326) called geom: And _assuming your lat/lon coordinates are referenced to WGS 84_ or, for small to medium scales, NAD 83---then EPSG:4326 is the right coordinate system. Not all lat/lon coordinates are equal. For example the error between mis-identifying lat/lon in NAD 27 as lat/lon in WGS 84 is about half a city block. -Charlie ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS 2.0.0 Released
On Tuesday, April 3, 2012 2:04:17 PM UTC-7, Paul Ramsey wrote: The PostGIS development team is super excited, can hardly believe that they are actually doing this, aren't maybe even sure that they are ready to make this kind of commitment, not so young, and not when we have so much more living to do, but: PostGIS 2.0.0 is complete and available for download. http://postgis.org/download/postgis-2.0.0.tar.gz The development process for 2.0 has been very long, but has resulted in a release with a number of exciting new features. * Raster data and raster/vector analysis in the database * Topological models to handle objects with shared boundaries * PostgreSQL typmod integration, for an automagical geometry_columns table * 3D and 4D indexing * Index-based high performance nearest-neighbour searching * Many more vector functions including * ST_Split * ST_Node * ST_MakeValid * ST_OffsetCurve * ST_ConcaveHull * ST_AsX3D * ST_GeomFromGeoJSON * ST_3DDistance * Integration with the PostgreSQL 9.1 extension system * Improved commandline shapefile loader/dumper * Multi-file import support in the shapefile GUI * Multi-table export support in the shapefile GUI * A geo-coder optimized for free US Census TIGER (2010) data We are greatly indebted to our large community of beta testers who valiantly tested PostGIS 2.0.0 and reported bugs so we could squash them before release time. And also we want to thank our parents for making PostGIS possible. Yours, The PostGIS development team Awesome news! However, the link to the manual for PostGIS 2.0 is currently returning a 404: http://postgis.refractions.net/documentation/manual-2.0 -Charlie ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Are there any Ubuntu PPAs that serve PostGIS 2.0 beta releases?
On Thursday, March 15, 2012 10:40:00 AM UTC-7, Charlie Sharpsteen wrote: Ok, I now have a nightly build set up that is tracking the PostGIS trunk. Binaries are being built for Oneric (11.11) and Precise (12.04-dev) ... I will ponder upgrading GEOS and GDAL and setting up a stable build for the most recent beta releases. The PostGIS nightly builds have been augmented with GEOS 3.3.2 and now include the topology plugin. GDAL 1.7.x is still being used from the official Ubuntu repositories. The installation steps are now: sudo apt-add-repository ppa:sharpie/for-science # To get GEOS 3.3.2 sudo apt-add-repository ppa:sharpie/postgis-nightly sudo apt-get update sudo apt-get install postgresql-9.1-postgis The build of GEOS 3.3.2 was created using the following steps: * Forking the official Ubuntu sources for GEOS 3.2.2. * Backporting GEOS from Debian Wheezy to get to 3.3.1 along with associated packaging updates. * Merging in 3.3.2 from the official GEOS tarball. This was accomplished by: bzr branch lp:ubuntu/geos cd geos bzr merge debianlp:wheezy/geos # De-stage all Debian patches prior to an upstream import quilt pop -a bzr merge-upstream # Finds the latest GEOS tarball and merges it in! quilt push -a # Fix any outdated patches. I had to fix one patch conflict caused by the last command, but otherwise everything went smoothly. GDAL is at 1.9.0 in Debian Experimental, but backporting looks like a moderate headache so I'm holding off for now. I may set up a stable build of PostGIS beta packages, but it looks like we are pretty close to 2.0 so I may wait until the official release. The nightly builds against the SVN trunk _should_ be pretty stable in the lead up to 2.0. -Charlie ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Are there any Ubuntu PPAs that serve PostGIS 2.0 beta releases?
On Wednesday, March 14, 2012 8:18:35 AM UTC-7, Charlie Sharpsteen wrote:
On Wednesday, March 14, 2012 12:02:24 AM UTC-7, Sandro Santilli wrote:
On Tue, Mar 13, 2012 at 07:50:32PM -0700, Charlie Sharpsteen wrote:
I was wondering if anyone has a Personal Package Archive for Ubuntu
that is
tracking the 2.0 beta releases. I looked at the UbuntuGIS and Postgis
project pages on Launchpad, but the only releases listed there were for
1.5.x. Even the unstable repostory of UbuntuGIS was still tracking
1.5.x.
I'm not aware of any. It'd be very welcome if you want to setup one.
--strk;
Allright, I'll take a shot at it. Looks like there is a Launchpad
repository that is synching with PostGIS SVN every 6 hours or so---perhaps
I can get a nightly build set up.
-Charlie
Ok, I now have a nightly build set up that is tracking the PostGIS trunk.
Binaries are being built for Oneric (11.11) and Precise (12.04-dev) and can
be installed thusly:
sudo apt-add-repository ppa:sharpie/postgis-nightly
sudo apt-get update
sudo apt-get install postgresql-9.1-postgis
Caveats:
- No build of `postgis` or `libpostgis-java`, the only package provided
is `postgresql-9.1-postgis` which contains the PostGIS plugins, extensions
and command line tools.
- Builds against GEOS 3.2.2, supplied by the standard Ubuntu
repositories, thus the topology extension is unavailable.
- Builds against GDAL 1.7.0, supplied by the standard Ubuntu
repositories, thus some raster functionalities are unavailable.
The raw materials that went into creating this build were the PostGIS
repository on Launchpad which synchs with the upstream SVN trunk ~every 6
hours:
https://code.launchpad.net/~registry/postgis/trunk
This repostory can be cloned using bazaar via `bzr branch lp:postgis`. The
other component is the official Ubuntu PostGIS repository that contains the
debian packaging materials:
https://code.launchpad.net/~ubuntu-branches/ubuntu/precise/postgis/precise
This repository can be cloned using `bzr branch lp:ubuntu/postgis`. My
strategy was to fork `lp:ubuntu/postgis`, edit the contents of the debian
subdirectory to make it compatible with 2.0-dev and then publish the edited
fork to Launchpad under my `+junk` section. After this was done, I set up a
`bzr-builder` recipe that fuses `lp:postigs`, which tracks the upstream
PostGIS repo, with the debian subdirectory contained in
`lp:~sharpie/+junk/postgis`, which contains my edited packaging info, to
create a nightly build:
# bzr-builder format 0.3 deb-version
{debupstream}~r{revno}~ppa{revno:packaging}
lp:postgis
nest-part packaging lp:~sharpie/+junk/postgis debian debian
Pretty slick. I will ponder upgrading GEOS and GDAL and setting up a stable
build for the most recent beta releases.
-Charlie
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Are there any Ubuntu PPAs that serve PostGIS 2.0 beta releases?
On Thursday, March 15, 2012 10:42:52 AM UTC-7, Stephen V. Mather wrote: Pretty slick. I will ponder upgrading GEOS and GDAL and setting up a stable build for the most recent beta releases. Wow. That would be awesome. GDAL should be pretty simple. The problem with GEOS is that it is pretty far down at the base of the GIS foodchain---so lots of libraries that are used by GDAL also use GEOS and would need to be updated as well. I doubt I will have the time to compile my way through everything between GEOS and GDAL. -Charlie ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Are there any Ubuntu PPAs that serve PostGIS 2.0 beta releases?
On Wednesday, March 14, 2012 12:02:24 AM UTC-7, Sandro Santilli wrote: On Tue, Mar 13, 2012 at 07:50:32PM -0700, Charlie Sharpsteen wrote: I was wondering if anyone has a Personal Package Archive for Ubuntu that is tracking the 2.0 beta releases. I looked at the UbuntuGIS and Postgis project pages on Launchpad, but the only releases listed there were for 1.5.x. Even the unstable repostory of UbuntuGIS was still tracking 1.5.x. I'm not aware of any. It'd be very welcome if you want to setup one. --strk; Allright, I'll take a shot at it. Looks like there is a Launchpad repository that is synching with PostGIS SVN every 6 hours or so---perhaps I can get a nightly build set up. -Charlie ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Are there any Ubuntu PPAs that serve PostGIS 2.0 beta releases?
On Wednesday, March 14, 2012 8:21:48 AM UTC-7, Denis Rouzaud wrote: That would be amazing. But, there is no ppa for geos 3.3 either and I am not sure about gdal 1.9... It might be a little early to have a ppa repo and dependencies with unavailable packages My 2 cents. Pretty sure I built PostGIS from source against GDAL 1.7 and GEOS 3.2.2 from the Oneiric standard repository. Are there any critical functions that are inactive without the latest and greatest versions of these libraries? -Charlie ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Are there any Ubuntu PPAs that serve PostGIS 2.0 beta releases?
On Wednesday, March 14, 2012 8:32:50 AM UTC-7, Denis Rouzaud wrote: You are right, but there notices in http://postgis.refractions.net/documentation/manual-svn/postgis_installation.html#id2744974 GEOS geometry library, version 3.2.2 or greater, but GEOS 3.3.2+ is recommended. Without GEOS 3.3, you will be missing some major enhancements with handling of topological exceptions and improvements to geometry validation and making geometries valid such as ST_ValidDetail and ST_MakeValid. GEOS 3.3.2+ is also required for topology support. GEOS is available for download fromhttp://trac.osgeo.org/geos/ http://trac.osgeo.org/geos/and 3.3+ is backward-compatible with older versions so fairly safe to upgrade. GDAL, version 1.6 or higher (1.9 or higher is preferable since some things will not work well with lower versions). This is needed for raster support and will be required in final release of PostGIS 2.0. http://trac.osgeo.org/gdal/wiki/DownloadSource. I am no expert, but I am not sure it's good idea to deliver builds of postgis in a sub-optimal config. Well, a sub-optimal build gets more work done than no build any day of the week. Plus, all of this work has to be done eventually---easier to improve something than start from scratch. It does look like a GDAL upgrade to 1.9 would be a good idea though if the final version will require it. I have plenty of experience building this stuff from maintaining most of the GIS stack for the Homebrew package manager on OS X. Entirely new to Debian packaging though, so that will slow me down quite a bit. -Charlie On Wednesday, March 14, 2012 8:32:50 AM UTC-7, Denis Rouzaud wrote: You are right, but there notices in http://postgis.refractions.net/documentation/manual-svn/postgis_installation.html#id2744974 GEOS geometry library, version 3.2.2 or greater, but GEOS 3.3.2+ is recommended. Without GEOS 3.3, you will be missing some major enhancements with handling of topological exceptions and improvements to geometry validation and making geometries valid such as ST_ValidDetail and ST_MakeValid. GEOS 3.3.2+ is also required for topology support. GEOS is available for download fromhttp://trac.osgeo.org/geos/ http://trac.osgeo.org/geos/and 3.3+ is backward-compatible with older versions so fairly safe to upgrade. GDAL, version 1.6 or higher (1.9 or higher is preferable since some things will not work well with lower versions). This is needed for raster support and will be required in final release of PostGIS 2.0. http://trac.osgeo.org/gdal/wiki/DownloadSource. I am no expert, but I am not sure it's good idea to deliver builds of postgis in a sub-optimal config. On 03/14/2012 04:29 PM, Charlie Sharpsteen wrote: On Wednesday, March 14, 2012 8:21:48 AM UTC-7, Denis Rouzaud wrote: That would be amazing. But, there is no ppa for geos 3.3 either and I am not sure about gdal 1.9... It might be a little early to have a ppa repo and dependencies with unavailable packages My 2 cents. Pretty sure I built PostGIS from source against GDAL 1.7 and GEOS 3.2.2 from the Oneiric standard repository. Are there any critical functions that are inactive without the latest and greatest versions of these libraries? -Charlie ___ postgis-users mailing listpostgis-users@postgis.refractions.nethttp://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Are there any Ubuntu PPAs that serve PostGIS 2.0 beta releases?
On Wednesday, March 14, 2012 8:40:22 AM UTC-7, Charlie Sharpsteen wrote: It does look like a GDAL upgrade to 1.9 would be a good idea though if the final version will require it. I have plenty of experience building this stuff from maintaining most of the GIS stack for the Homebrew package manager on OS X. Entirely new to Debian packaging though, so that will slow me down quite a bit. -Charlie Actually, after re-reading the installation notes, it doesn't look like GDAL 1.9 is mandatory---just that GDAL = 1.6 will be mandatory once PostGIS 2.0 is released. -Charlie ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Are there any Ubuntu PPAs that serve PostGIS 2.0 beta releases?
On Wednesday, March 14, 2012 8:44:16 AM UTC-7, Charlie Sharpsteen wrote: On Wednesday, March 14, 2012 8:40:22 AM UTC-7, Charlie Sharpsteen wrote: It does look like a GDAL upgrade to 1.9 would be a good idea though if the final version will require it. I have plenty of experience building this stuff from maintaining most of the GIS stack for the Homebrew package manager on OS X. Entirely new to Debian packaging though, so that will slow me down quite a bit. -Charlie Actually, after re-reading the installation notes, it doesn't look like GDAL 1.9 is mandatory---just that GDAL = 1.6 will be mandatory once PostGIS 2.0 is released. -Charlie Allright, took a few jabs at this and here is what I have found so far: - Without GEOS 3.3.2, PostGIS must be configured `--without-topology` so this definitely knocks out a big chunk of 2.0 functionality. - 2.0.0beta2 can compile against the dependencies supplied for PostGIS 1.5.x via `apt-get build-dep postgis` with the addition of GDAL 1.7.0 via `apt-get install libgdal1-dev`. However, 2.0.0beta3, released today, and the current SVN head fail during configure: RASTER: Raster support requested checking for GDAL = 1.6.0... found checking gdal.h usability... yes checking gdal.h presence... yes checking for gdal.h... yes checking ogr_api.h usability... yes checking ogr_api.h presence... yes checking for ogr_api.h... yes checking cpl_conv.h usability... yes checking cpl_conv.h presence... yes checking for cpl_conv.h... yes checking for GDALAllRegister in -lgdal... no configure: error: could not find gdal This is odd, because running nm against the libraries shows that the symbol is there: vagrant@vagrant-64:~/postgis-2.0.0beta3$ nm --dynamic /usr/lib/libgdal1.7.0.so | grep GDALAllRegister 0021a820 T GDALAllRegister Don't know if this is a bug in the latest beta, or a problem with Ubuntu's GDAL 1.7.0 libraries. Any advice would be appreciated. -Charlie ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Are there any Ubuntu PPAs that serve PostGIS 2.0 beta releases?
On Wednesday, March 14, 2012 1:00:37 PM UTC-7, Charlie Sharpsteen wrote: - 2.0.0beta2 can compile against the dependencies supplied for PostGIS 1.5.x via `apt-get build-dep postgis` with the addition of GDAL 1.7.0 via `apt-get install libgdal1-dev`. However, 2.0.0beta3, released today, and the current SVN head fail during configure: RASTER: Raster support requested checking for GDAL = 1.6.0... found checking gdal.h usability... yes checking gdal.h presence... yes checking for gdal.h... yes checking ogr_api.h usability... yes checking ogr_api.h presence... yes checking for ogr_api.h... yes checking cpl_conv.h usability... yes checking cpl_conv.h presence... yes checking for cpl_conv.h... yes checking for GDALAllRegister in -lgdal... no configure: error: could not find gdal This is odd, because running nm against the libraries shows that the symbol is there: vagrant@vagrant-64:~/postgis-2.0.0beta3$ nm --dynamic /usr/lib/ libgdal1.7.0.so | grep GDALAllRegister 0021a820 T GDALAllRegister Don't know if this is a bug in the latest beta, or a problem with Ubuntu's GDAL 1.7.0 libraries. Any advice would be appreciated. -Charlie Found the problem in config.log: configure:19617: gcc -o conftest -I/usr/include/gdal conftest.c -lgdal -L/usr/lib -lgdal1.7.0 5 /usr/bin/ld: cannot find -lgdal The configure script is trying to link against both libgdal and libgdal1.7.0, but libgdal.so does not exist, only libgdal1.7.0.so. According to config.log, this has been identified and stored in LIBGDAL_LDFLAGS: LIBGDAL_LDFLAGS='-L/usr/lib -lgdal1.7.0' But the test scripts are still trying to pass `-lgdal`. Looks like a bug in the PostGIS configure script. -Charlie ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Are there any Ubuntu PPAs that serve PostGIS 2.0 beta releases?
On Wednesday, March 14, 2012 1:05:14 PM UTC-7, Sandro Santilli wrote: On Wed, Mar 14, 2012 at 01:00:37PM -0700, Charlie Sharpsteen wrote: - Without GEOS 3.3.2, PostGIS must be configured `--without-topology` so this definitely knocks out a big chunk of 2.0 functionality. Yep, what about a GEOS ppa ? --strk; Possible, but i'm trying to start simple---the fewer plates spinning on the stage the better. -Charlie ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Are there any Ubuntu PPAs that serve PostGIS 2.0 beta releases?
Hello, I was wondering if anyone has a Personal Package Archive for Ubuntu that is tracking the 2.0 beta releases. I looked at the UbuntuGIS and Postgis project pages on Launchpad, but the only releases listed there were for 1.5.x. Even the unstable repostory of UbuntuGIS was still tracking 1.5.x. -Charlie ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] postgis-2.0.0alpha5
PostGIS 2.0 pre-releases are now available to OS X users through the Homebrew package manager http://mxcl.github.com/homebrew. Install thusly: brew install postgis --devel One data point: Compiled 2.0alpha5 using Clang from Xcode 4.3 and it appears to be working nicely---at least for the toy problems I ran through it. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
