Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
50
votes
12answers
110k views

Running Create Extension postgis gives ERROR could not open extension control file?

I have installed PostgreSQL 9.x and PostGIS 1.5/2.0 several times and have never had this problem. I just got a new CentOS 6.3 server up and running and and I have Postgres 9.3 functioning as ...
49
votes
3answers
11k views

What is the purpose of PostGIS on PostgreSQL?

PostgreSQL already supports spatial data types, operators and indexing. What does PostGIS provide exactly that made it necessary to exist as an extension to PostgreSQL? Why don't we all just use ...
36
votes
5answers
35k views

Why does PostGIS installation not create a template_postgis?

I went this route for installation of PostgreSQL and PostGIS: PostgreSQL using the standalone KyngChaos, PostGIS install from KyngChaos as well... pgAdmin3 from PostgreSQL Tools Now when I launch ...
35
votes
2answers
35k views

How do I check which version of GDAL I have and how many?

I have installed in my computer the following software: PosgresSQL 9.3.2.3 PostGIS 2.1.1.1 QGIS 2.0.1 Dufour (this one installed through the OSGeo4w package) QGIS 1.7.0 Wroclaw As far as I know all ...
35
votes
0answers
18k views

How to use PostgreSQL/PostGIS with ArcGIS for Desktop? [duplicate]

Has anyone used PostgreSQL with PostGIS successfully? I am a semi-experienced ArcGIS user and want to build out a database and connect it to ArcCatalog. I've scoured the internet for resources but ...
32
votes
5answers
19k views

SQL query to have a complete geojson feature from PostGIS?

I would like to get a geojson feature with properties from PostGIS. I have found an example to have a feature collection but I can't make it works for just a feature. SELECT row_to_json(fc) FROM ( ...
30
votes
3answers
11k views

Which function for creating a POINT in PostGIS?

When defining a Point in PostGIS, when do you decide to use which of the following? ST_SetSRID(ST_MakePoint(lon,lat),4326) ST_SetSRID(ST_Point(long,lat),4326) ST_SetSRID(ST_GeomFromText('POINT(lon ...
29
votes
2answers
45k views

Adding shapefiles to PostGIS database

I was able to create a Postgresql database and then I executed, createlang plpgsql geospecies psql -d geospecies -f postgis.sql psql -d geospecies -f spatial_ref_sys.sql So now can I directly import ...
27
votes
9answers
15k views

Working with PostGIS data in R?

I work with R almost all the time, and now I am using it for doing spatial data mining. I have a PostGIS database with (obviously) GIS data. If I want to make statistical spatial analysis and plot ...
27
votes
6answers
11k views

How can I update ogr2ogr to include the PostgreSQL driver?

I'm trying to use ogr2ogr to upload a shapefile to a remote PostGres installation. When I ran this command: celenius:~ celenius$ ogr2ogr -f PostgreSQL PG:"host=255.34.00.00 user=postgres dbname=mydb ...
27
votes
5answers
14k views

Optimizing osm2pgsql imports for OSM data

I'm currently building an instance on EC2 on which to import the entire Planet.osm snapshot of the whole Earth's worth of data for some projects we're working on. I've spun up a large Ubuntu x64 ...
24
votes
3answers
31k views

How to create a new 'gis' database in PostGIS?

I'd like to create a new database in PostGIS, so I can load stuff into it while the current database is being used. According to the docs Some packaged distributions of PostGIS (in particular the ...
24
votes
2answers
7k views

Mixing geometry types in one PostGIS table

I'm faced with the following problem. I have to migrate from Oracle database to PostgreSQL+PostGIS. Currently all geometries of all types are stored in one table and each record contains a "lid" field ...
24
votes
2answers
15k views

In PostGIS is it possible to create a View with a unique ID?

When I create a view in PostGIS is there any way to add a unique ID to that view? Just like the "gid" field in any other PostGIS table? Edit: Sorry I should have included this in the original post. I ...
24
votes
2answers
697 views

Seeking Algorithm to detect circling and beginning and end of circle?

I've got lots of flight data from glider pilots in the form of gps fixes in a fixed interval. I would like to analyze the flight path and detect the start and the end of the 'circling' the glider ...
23
votes
3answers
15k views

Postgis TopologyException: side location conflict at 226.001 39.5158, what's the problem?

after st_union() PgSQL returns a note: NOTE: TopologyException: side location conflict at 226.001 39.5158 And in the results there are two rows with empty geometry and other rows are ok. What ...
22
votes
4answers
6k views

How can I optimize pgrouting for speed?

I am using pgrouting on a postgis database created through osm2pgrouting. It performs very good on a limited dataset (3.5k ways, all shortest path A* searches < 20 ms). However since I have ...
22
votes
3answers
7k views

Poor performance with storing large rasters in PostGIS and visualising in QGIS

my question concerns the use and performance of several software tools in conjunction, namely PostgreSQL, PostGIS, QGIS, and GDAL. I'm a long-time ArcGIS, Python, and R user who is interested in ...
22
votes
2answers
5k views

Publishing PostGIS Rasters in GeoServer?

How do I publish PostGIS rasters using GeoServer? I have spent a lot of time trying to create a Raster datasource using Image Mosaic JDBC but with no luck. Steps performed: 1. Downloaded and ...
21
votes
1answer
3k views

Can PostGIS and QGIS provide dependable multi-user editing?

After considering a few options, I'm trying to give my 15-20 QGIS 2.2 users the ability to concurrently view/edit one or two basic point PostGIS layers, largest one has just 16,000 records. I've ...
20
votes
4answers
16k views

Shapefile loader in pgAdmin 4

Recently I have updated my postgresql version. I have also upgraded pgAdmin from 3 to 4. But I was unable to load shapefile. I didn't find any plugin called shapefile loader. How can I get the ...
20
votes
3answers
26k views

Creating spatial tables with PostGIS

In the PostGIS documentation it says that there are two steps to creating a spatial table with SQL: Create a normal non-spatial table. Add a spatial column to the table using the OpenGIS "...
20
votes
8answers
26k views

How can I enable Shapefile GUI Loader in pgadmin3?

I have installed PostgreSQL 9.1 with PostGIS 1.5.3 on Ubuntu 11.10. I also installed pgadmin3 1.14.0-beta1, following this instructions. If I remember correctly, when I installed PostGIS on Windows ...
18
votes
3answers
17k views

Loading a raster into a PostGIS 2.0 database on Windows

I'm trying to figure out how to load a raster into a PostGIS2.0 database (I have asked previous questions on this topic here and here). I am trying to use the raster2pgsql.exe program provided with ...
18
votes
1answer
239 views

Can we create views over a raster table in Postgis2.0?

Is it possible to create PostgreSQL views over a table that contains raster data? I have succeeded in creating the view but the entry in raster_columns is not correct (all the raster info is missing)....
17
votes
3answers
8k views

Identifying road intersections using PostGIS

I'm trying to identify where roads intersect each other, and to make a point at this intersection, with the number of roads that form the intersection listed. I was wondering if there was some way ...
17
votes
3answers
3k views

How fast should I expect PostGIS to geocode well-formatted addresses?

How fast should I expect PostGIS to geocode well-formatted addresses? I've installed PostgreSQL 9.3.7 and PostGIS 2.1.7, loaded the nation data and all states data but have found geocoding to be much ...
17
votes
1answer
714 views

Query OSM for all beaches facing west? - QGIS preferred

I want to identify all beaches on this planet (or a given subset of it) that are facing westwards (+/- 20-30 degrees). My idea is to use OSM as the data source. Extracting all polygons that have the ...
16
votes
1answer
6k views

How to properly set up indexes for PostGIS distance queries?

I'm building an application that is supposed to query and return every Record in a table that is X kilometers away from PointX. Records and PointX's positions are determined from (long/lat) ...
16
votes
2answers
15k views

Finding the closest Geometry in PostGIS

I've been looking over the PostGIS "API" of functions, and I notice that most of them take two elements to compare. For example, the ST_Distance function takes two geometry/geography elements to find ...
15
votes
3answers
8k views

How to get the centroid of a set of points in PostGIS?

I use PostgreSQL with the PostGIS extension. I have a set of points in the_geom column from a table myschema.myobjects. I want to create a select statement to get the centroid of this cluster, so ...
15
votes
2answers
8k views

PostgreSQL/PostGIS spatial index - no speed up

I have a spatial table in a PostgreSQL/PostGIS database. Every row in it represents a Polygon. It is of following form: +----+--------+ |gid | way | +----+--------+ |241 | 01030..| The geometric ...
15
votes
10answers
18k views

Broken packages and unmet dependency installation QGIS, PostgreSQL and postgis Ubuntu 14.04

I am aware that this could be a duplicate topic but I am struggling for a several days. I upgraded QGIS from 2.8 to 2.18 and after that everything get broken. I couldn't use PostGIS extension on ...
15
votes
1answer
2k views

Where does the default Earth radius in ST_Distance_Sphere come from?

MySQL says in the docs for ST_Distance_Sphere Calculations use a spherical earth and a configurable radius. The optional radius argument should be given in meters. If omitted, the default radius is ...
15
votes
2answers
10k views

GUI tools for PostgreSQL/PostGIS?

as a cartographer I work with TileMill to visualize geodata. I installed a local PostgreSQL server with PostGIS and osm2pgsql. Everything works fine. Anyhow, I am new to working with servers and the ...
15
votes
6answers
18k views

How to use ST_Intersection?

Here's a quick summary about what I'm trying to do: I have 3 tables in Postgres, 'a' and 'b', each have a Polygon column, and 'c' has a Point column. What I'm trying to do here is to get the ...
15
votes
3answers
17k views

Get Lon Lat values from a GEOGRAPHY data type in PostGIS

After storing lon lat values as a GEOGRAPHY data type, how do I retrieve the individual lat lon values? Failed attempt: SELECT id, geog, ST_X(geog), ST_Y(geog) FROM locations; Error: No function ...
15
votes
3answers
7k views

Add PostGIS spatial functions to a custom schema other than “public” in PostgreSQL

Recently, I created a PostGIS 2.0.3 database on a PostgreSQL 9.1 database server using pgAdmin. The "PostGIS" extension was found installed in "Extensions". Al spatial functions were added to the "...
15
votes
1answer
593 views

How to extract sublinestring from a linestring with overlapping segments

Problem statement: Given a bus route (defined as a linestring ln.the_geom), and a set of bus stop locations (pt1.the_geom,pt2.the_geom,...ptn.the_geom), divide the bus route into segments between bus ...
14
votes
3answers
11k views

calculating percent area of intersection in where clause

I have a table of polygons (census block groups) in postgres. I want to tag each block group with the town (another polygon table) it mainly resides within. Is this possible? I'm thinking I'd need to ...
14
votes
4answers
9k views

Implementing Spatial Autocorrelation using QGIS or PostgreSQL or any other free application?

I am a total newbie on the GIS scene so I hope this is not a stupid question. I have two layers in a PostgreSQL database. The first layer contains points where events happened in Europe. The second ...
14
votes
1answer
792 views

Nearest Neighbor Grouped Selection in QGIS

I have a list containing over 100,000 points in lat/long format which I have imported into qgis. Now, what I am trying to do here is group all of these points into box groups and by that I ...
14
votes
2answers
5k views

Huge point cloud laser data in PostGIS - Storing and Processing it

I wonder, how it's possible to store huge sets of laser scanned point cloud data in PostGIS, with the time-aspect for processing it in mind. I know, there exists a geometry-object Point in PostGIS. ...
13
votes
2answers
8k views

Setting up PostGIS database and opening it in QGIS on Windows?

I have tried to follow the manuals for setting up a PostGIS database to be accessed by users with QGIS, but at the moment I cannot understand enough to progress? My situation; I am a trainee ...
13
votes
3answers
4k views

PostgreSQL trouble editing points / lines / polygons in QGIS

I have a problem with postgresql and QGIS : I can add points/lines/polygons and fillup field But I can't : delete points/lines/polygons modify points/lines/polygons (move or modify field) ex : ...
13
votes
2answers
15k views

How do I get the distance meter value between two geometries in PostGIS?

I have a simple question about calculating distances in PostGIS. I would like to get the distance between two geometries. I am using this sid : 4269 in meters what i am doing now is this : ...
13
votes
4answers
12k views

Percentage of polygon in one shapefile within polygon of another

I'm a newbie, apologies if this is obvious/has already been asked and answered but I couldn't find anything. I have two shapefiles: 1. an administrative boundary layer for a county in the UK known as ...
13
votes
3answers
1k views

Centralizing shapefile data into database

I've got hundreds of shapefiles from various different GIS projects that I want to start consolidating into a single database platform, currently attempting this with Postgres / PostGIS. Hardly any ...
13
votes
1answer
7k views

What is difference between ST_DWithin and ST_Distance for proximity search in PostGIS?

I have records stored in a table with latitude/longitude coordinates stored in a geometry field. I want to find all records nearby a user supplied reference point. Note "nearby" probably means less ...
13
votes
4answers
11k views

Connecting to ArcGIS Desktop PostgreSQL database (running locally)

I know this is a total noob question but then again, when it comes to PostgreSQL I am a total noob... I have installed the OpenGeo suite on my computer, running Windows 7, 64-bit. I have ...