PostgreSQL with PostGIS for mapping coordinates

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

You can download PostGIS from here.

Http://postgis.net/source

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.

=================8x----------------------------------x8============================

Http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS20Ubuntu1204src

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 http://download.osgeo.org/geos/geos-3.3.9.tar.bz2tar xfj geos-3.3 . 9. Tar . BZ2CD geos-3.3. 9 . /ConfiguremakesudomakeinstallCD .

[NEW ADDED]

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

http://www.gdal.org/

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 http://download.osgeo.org/postgis/source/postgis-2.0.6.tar.gztar 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:

CREATE EXTENSION PostGIS;

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.

PostGIS:

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://help.ubuntu.com/community/PostgreSQL

=================8x----------------------------------x8============================

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