Tag Archives: PostGIS

Create a FeatureCollection object from a PostGIS query

Usually to render spatial data on a web map you need GeoJSON data  of the type “FeatureCollection”, and if you have your data on a  PostGIS database you can do a query to get  the geometries on GeoJSON format with the ST_AsGeoJSON(geom) function. Something like this:

SELECT loc_id, loc_name, ST_AsGeoJSON(geom, 4) as geojson FROM locations;

This will return a different row for every feature that should be on the collection, so after run that query you need a function to iterate over every result to insert loc_id and loc_name as properties of every feature and push them to the array of features.

But there is a more efficient way of doing it with this single trick query:

SELECT row_to_json(fc)
FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
   FROM (SELECT 'Feature' As type
    , ST_AsGeoJSON(lg.geom, 4)::json As geometry
    , row_to_json((SELECT l FROM (SELECT loc_id, loc_name) As l
      )) As properties
   FROM locations As lg ) As f ) As fc;

Continue reading

Install postgis 2.1 and QGIS on ubuntu 15.04

This is tested on a fresh installation of Ubuntu 15.04.


$ sudo apt-get install postgresql postgresql-contrib postgis postgresql-9.4-postgis-2.1 pgadmin3

# add a new user (need to be authenticated as a superuser)
$ sudo -u postgres createuser --superuser mysuperuser

# Set password for created user
$ sudo -u postgres psql postgres
postgres=# \password mysuperuser

Enter new password:mysuperuserpassword
Enter it again:

# create password for user postgre
$ sudo su
$ su - postgres

Now you can access the PostgreSQL prompt with the command:
$ psql

And then change the password for postgres role by typing :
$ \password postgres
Enter new password: postgres

Continue reading

Selección por rectángulo en CartoDB

CartoDB funciona con PostGIS así que podemos usar los mismos comandos, salvo alguna pequeña diferencia que luego veremos.

Primero hay que crear un polígono rectangular que nos sirva para hacer la selección de aquellos puntos contenidos dentro de el. Usamos ST_MakeEnvelope, que crea un rectángulo a partir de unas x/y mínimas y máximas:

ST_MakeEnvelope(double precision xmin, double precision ymin, double precision xmax, double precision ymax, integer srid=unknown);

Continue reading