PostgreSQL with PostGIS for mapping coordinates

Source: Internet
Author: User
Tags psql postgis postgres createdb

For the based service, I try to use PostgreSQL with PostGIS.

You can download PostGIS from here.


It is recommended so need to download and compile yourself since there was many packages dependencies need to be Don E.

Here is a tutorial which was very handy if you were using ubuntu12.04/mint13 or its derived ones.



How to install PostGIS 2.0 on Ubuntu 12.04 LTS ( Precise) from Sourceprerequisites

Several components is needed, which can either be built from source or installed from pre-built packages, as shown below.

Install Prerequisite Packages using:

sudo Install build-essential postgresql-9.1 postgresql-server-dev-9.1 Libxml2-dev Libproj-dev Libjson0-dev Xsltproc docbook-xsl Docbook-mathml

Optional Package for raster support (this is required if you want to build the PostgreSQL extensions):

sudo Install Libgdal1-dev

Build GEOS 3.3.x

PostGIS 2.0 requires GEOS >= 3.3.2 for topology support, however Ubuntu 12.04 only have GEOS 3.2.2 available in packages , so it needs to is built from source. If you don ' t need topology, you don't need to build this component, but it's highly recommended.

There is multiple ways to build GEOS, but this is the simplest:

wget xfj geos-3.3 . 9. Tar . BZ2CD geos-3.3. 9 . /ConfiguremakesudomakeinstallCD .


Since there is another package which needs a higher version.

Need to install Gdal-config (version 1.8.0+). By default Ubuntu 12.04 is of 1.7.3

Download the source codes and compile as you want.

cd {The_souce_codes_folder}

sudo ./configure--prefix=/usr/local/sudomakesudomakeInstall  sudo ldconfig

Then your may compile the PostGIS package.

Build PostGIS
wget xfz postgis-2.0. 6. Tar . GZCD PostGIS-2.0. 6

PostGIS 2.0 can be configured to disable topology or raster components, using the Configure Flags --without-raster and/or --without-topology. The default is to build both. Note that raster are required for the extension installation method for PostgreSQL.

./Configuremakesudomakeinstallsudo  ldconfig  sudo make comments-install

Lastly, enable the command-line tools to work from your shell:

sudo Ln -sf/usr/share/postgresql-common/pg_wrapper/usr/local/bin/shp2pgsqlsudoln -sf/usr/share/postgresql-common/pg_wrapper/usr/local/bin/pgsql2shpsudoln -sf/usr/ Share/postgresql-common/pg_wrapper/usr/local/bin/raster2pgsql

Spatially enabling a database

With PostgreSQL 9.1, there is, methods to add PostGIS functionality to a database:using extensions, or using enabler Scripts.

PostGIS Extension for PostgreSQL

Spatially enabling a database using extensions is a new feature of PostgreSQL 9.1.

Connect to your database using Pgadmin or PSQL, and run the following commands. To add PostGIS with raster support:


To add topology support, a second extension can is created on the database:

CREATE EXTENSION postgis_topology;

Enabler scripts/template

Enabler scripts can be used to either build a template, or directly spatially enable a database. This method was older than the extension method, but was required if the raster support was not built.

The following example creates a template, which can be re-used for creating multiple spatially-enabled databases. Or If you just want to make one spatially enabled database, you can modify the commands for your needs.


sudo-u postgres createdb template_postgissudo-U postgres psql-d template_postgis-c"UPDATE pg_database SET datistemplate=true WHERE datname= ' Template_postgis '"sudo-U postgres psql-d template_postgis-f/usr/share/postgresql/9.1/contrib/postgis-2.0/Postgis.sqlsudo-U postgres psql-d template_postgis-f/usr/share/postgresql/9.1/contrib/postgis-2.0/Spatial_ref_sys.sqlsudo-U postgres psql-d template_postgis-f/usr/share/postgresql/9.1/contrib/postgis-2.0/postgis_comments.sql

With raster support:

sudo -u postgres psql-d template_postgis-f/usr/share/postgresql/9.1/contrib/postgis-2.0 /rtpostgis.sqlsudo -u postgres psql-d template_postgis-f/usr/share/postgresql/9.1/ contrib/postgis-2.0/raster_comments.sql

With topology support:

sudo -u postgres psql-d template_postgis-f/usr/share/postgresql/9.1/contrib/postgis-2.0 /topology.sqlsudo -u postgres psql-d template_postgis-f/usr/share/postgresql/9.1/ contrib/postgis-2.0/topology_comments.sql

See Also
    • Https://


In the database console, try these!!

Connect to your database with psql or pgadmin. Run the following SQL:

-- Enable PostGIS (includes raster) CREATE EXTENSION PostGIS; -- Enable Topology CREATE EXTENSION postgis_topology; -- fuzzy matching needed for Tiger CREATE EXTENSION Fuzzystrmatch; -- Enable US Tiger Geocoder CREATE EXTENSION Postgis_tiger_geocoder;

For spatial objects!

--Create table with spatial columnCREATE TABLEmytable (ID SERIALPRIMARY KEY, Geom GEOMETRY (Point,26910), nameVARCHAR( -)); --Add a spatial indexCREATE INDEXMytable_gix onmytable USING GIST (GEOM); --Add a pointINSERT  intoMyTable (GEOM)VALUES(St_geomfromtext ('Point (0 0)',26910)); --Query for nearby pointsSELECTID, name frommytableWHERESt_dwithin (Geom, St_geomfromtext ('Point (0 0)',26910),   +); 

If You system alerts that "

gis=# Create extension Fuzzystrmatch;
Error:could not open extension control file "/usr/share/postgresql/9.1/extension/fuzzystrmatch.control": No such file or Directory


, try to

sudo Install postgresql-contrib-9.1 -y

Then, you'll see something:

GIS=Create  extension fuzzystrmatch; CREATE Extensiongis = Create extension Postgis_tiger_geocoder; CREATE Extensiongis =

Notice the ' GIS ' is the name of the database.

PostgreSQL with PostGIS for mapping coordinates

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.