4

I just upgraded my development machine to Windows 7 and am therefore reinstalling PostgreSQL and PostGIS. I used the Postgres Plus installer, as recommended on the Postgres website, and followed this tutorial for installation. I created a database using the template_postgis and the application that I am currently developing was able to connect to the database with no problems.

However, my old code is not able to create tables using the PostGIS Geography like it used to. For example, when I try to create the following table:

CREATE TABLE test_area (
    id SERIAL PRIMARY KEY,
    name VARCHAR(1000),
    area GEOGRAPHY(POLYGON, 4326),
    start_time DATE,
    end_time DATE
)

I get the following error:

ERROR:  type "geography" does not exist
LINE 4:         area GEOGRAPHY(POLYGON, 4326),
                     ^

If I look under the database in the Object brower of pgAdmin III, I see a bunch of Geometry functions, so the installation seems to have run correctly. I'm not sure what my problem might be. Is it possible for the installer to only install PostGIS Geometry, but not Geography?

5
  • I also had this problem previously: gis.stackexchange.com/questions/11886/… When you created a database did you copy the template database? I think that was one of my many mistakes.....
    – djq
    Aug 9, 2011 at 19:40
  • Thanks for the link. Based on that, I think my problem is that I installed PostGIS 1.4, which doesn't support Geography. I can't figure out how to install PostGIS 1.5 though. When I download the Windows 1-click installer from the Postgres website it only seems to come with PostGIS 1.4. I can download 1.5 binaries from the PostGIS website, but I can't figure out how to install them.
    – Steph
    Aug 9, 2011 at 20:54
  • Hmm, I don't think that is your problem as it should work with both AFAIK. I found the OpenGeo site very helpful for learning more: workshops.opengeo.org/postgis-intro
    – djq
    Aug 9, 2011 at 21:09
  • @celenius - I'm pretty sure that was my problem. According to this blog and a couple other sources I looked at, they say Geography was just added in PostGIS 1.5.
    – Steph
    Aug 10, 2011 at 0:08
  • Apologies! I did miss that you were using 64bit also... that was another of my errors.
    – djq
    Aug 10, 2011 at 1:27

2 Answers 2

3

As lovely as they are for developing / maintaining the one-click installer for Windows, EnterpriseDB makes it very easy to end up with their proprietary "Postgres Plus" software, which is not the community version and, unless you have a specific need for EnterpriseDB software, IT'S NOT WHAT YOU WANT.

It takes them time to port their add-ons to the latest versions and most of the additional components shipped with Postgres Plus (standard or advanced) are outdated. ALSO, they monkey with the file paths we reference so often when customizing our Postgres instances.

I suggest you thoroughly wipe your new system of all postgres plus or enterprise db components and start fresh with the installer found here:

http://www.enterprisedb.com/products-services-training/pgdownload#windows

Run this installer, selecting PostGIS from the Application Stack Builder, and you'll have the most recent, stable, community-supported versions of PostgreSQL and PostGIS.

I have nothing against the truly fine folks at EnterpriseDB, I just think they make it suspiciously easy for people looking for open-source software to end up installing their proprietary product.

2
  • Thank you for the advice. I didn't want to use the "Postgres Plus" proprietary version, but it seemed like what they were recommending on the Postgres website and I got tricked into it. I uninstalled what I had and installed the version you linked. That all went fine, but Stack Builder is not showing PostGIS or the "Spatial Extensions" option it should be under. I don't know why it's not included in this...
    – Steph
    Aug 9, 2011 at 23:23
  • Okay, I uninstalled the 64-bit version and tried it with the 32 and that included the PostGIS extension in Stack Builder. Finally! Thanks for your help!
    – Steph
    Aug 9, 2011 at 23:49
3

In my case, the answer was really simple:

I needed to make the create extension and create table before the set seach_path to inserts

This will not work:

SET search_path = myschema, pg_catalog;

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;
CREATE EXTENSION IF NOT EXISTS adminpack;

CREATE TABLE IF NOT EXISTS myschema.mytable ( something geography(POLYGON,4326), ... );

INSERT INTO mytable ... 

But, this will work:

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;
CREATE EXTENSION IF NOT EXISTS adminpack;

CREATE TABLE IF NOT EXISTS myschema.mytable ( something geography(POLYGON,4326), ... );

SET search_path = myschema, pg_catalog;

INSERT INTO mytable ... 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.