Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
13
votes
4answers
9k views

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

I new to GIS. I have two layers in a PostgreSQL database. The first layer contains points where events happened in Europe. The second contains a shapefile of Europe. I have been able to map the ...
13
votes
1answer
14k views

Import a shapefile to postgis with ogr2ogr gives: Unable to open datasource

I want to use ogr2ogr in order to import a shapefile in a postgis database. I have successfully installed ogr2ogr and I run from the pgsql the following command: ogr2ogr -f "PostgreSQL" PG:"host=...
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
2k views

How to get the length of a line inside a polygon

How can I get the length (brown marked part) of the "Road 7" inside the red polygon? My start: SELECT ST_Length(way) FROM lines WHERE road = "Road 7" AND ST_Intersects(line, polygon)? With that, I ...
13
votes
2answers
6k views

What are benefits of using ArcGIS with PostgreSQL over simply ArcSDE?

I understand that in order to benefit from ArcGIS spatial datatypes (ST_GEOMETRY, versioned geodatabases etc.) ArcSDE needs to be installed - and with the 10.1 client it is automatically installed (...
13
votes
1answer
4k views

How to perform a spatial join of point and polygon layers in PostGIS?

I have a point dataset representing households that I want to associate with a parcel layer (i.e. assign its parcel_id). With ArcMap, I could spatially join the polygons to the points and specify that ...
13
votes
1answer
789 views

Combined Hstore key/value and spatial query too slow to handle bigger OSM extracts

I'm trying to compute some statistics for OSM data using PostgreSQL 9.3.5 and PostGIS 2.1.4. I started with a small bavaria extract which I downloaded from Geofabrik. The db schema is the normal API 0....
13
votes
1answer
384 views

Accessing the internal data structure storing Postgis spatial index (PostGres GiST)

I am curious about how the internal data structures and storage mechanism of the spatial GiST index in Postgis. In Oracle, I used to like to show how the SDO indexes were "just another table", and how ...
12
votes
2answers
12k views

Cleaning geometries in PostGIS?

I am trying to do some processing on some very large polygon layers. However I am running into various geometry errors such as: NOTICE: Ring Self-intersection at or near point 470396.52017068537 ...
12
votes
4answers
15k views

I have not found shp2pgsql in postgis installation

I have been installed postgis following this guide. All is OK. SELECT postgis_full_version(); "POSTGIS="2.1.7 r13414" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, ...
12
votes
2answers
258 views

Select polygons with 5 or more points

I have two layers (polygon and point) in QGIS added from PostgreSQL database and want to select polygons from index grid with 5 or more points on them. Is this possible to do in QGIS or in PgAdmin? ...
12
votes
2answers
11k views

Multipoint vs point PostGIS

Is it normal to use the geometry type 'multipoint' instead of 'point' knowing that I use it to store only one point? How can I convert from multipoint to point?
12
votes
4answers
1k views

What is a suitable Global/Universal Unique Identifier for a PostGIS database?

I have read that using OIDs as a primary key in a postgreSQL/PostGIS db is poor practice because there are instances where these can be reset. Sounds logical, but then what is a suitable alternative? ...
12
votes
2answers
3k views

Getting multiple shortest paths with PgRouting in one query?

I want to run shortest path algorithm on multiple source&target pairs at once and get a result as a table and process it then. How do I do this? This query does not work: SELECT a.source, a....
12
votes
1answer
2k views

Materialized View Does Not Appear In Geometry_Columns View

I have defined a materialized view in a PostGIS 2.1-enabled database running PostgreSQL 9.3.1: CREATE MATERIALIZED VIEW canvec_data.contours_m AS SELECT contours_original.ogc_fid, ...
12
votes
5answers
4k views

Speeding up OpenStreetMap PostGIS querying

I have OpenStreetMap data for the Netherlands loaded into a PostGIS database (PostgreSQL 8.3 / PostGIS 1.3.3) using the osmosis schema. This means all tags are stored in a hstore field. In addition to ...
12
votes
2answers
13k views

Import large CSV file into PostGIS

I am trying to import CSV files into PostGIS. Following this post, I have created tables before. I found other suggestions saying that I can run the copy command. If I run this command: COPY table ...
12
votes
4answers
1k views

Open Street Map and PostgreSQL what do I exactly need?

I make my first steps in the GIS world and everything is very new for me even if I work on a "huge" project (ie: for a single programmer) very related to maps. My project needs to know informations ...
12
votes
2answers
797 views

Simplify the straight sections of (multi)Linestring in PostgreSQL/PostGIS leaving the curved sections untouched

I have (multi)linestrings containing upwards of 5000 points and I want to perform two simplifications possibly using Ramer Douglas Peucker or alternative methods in PostGIS. The (multi)linestrings ...
11
votes
1answer
18k views

Simple PostGIS ST_Distance query for two points from the same geometry table?

Note: This question was published at Stackoverflow at first. I have a geometry table points_table with SRID 4326 and a few points in it. I need to find a distance between a pair of points (features) ...
11
votes
1answer
7k views

Visualising a raster PostgreSQL table in QGIS

I have imported a raster tif image into PostgreSQL with this command: raster2pgsql -s 32643 -I -M filepath.tif -F -t 100x100 public.databassename > filepath.sql And imported the output SQL file ...
11
votes
2answers
2k views

Similarity between two or more trajectories

I have the data of trucks (http://www.chorochronos.org/). This data are gps coordinates of multiple trajectories of trucks in Athens. I have to calculate the similarity between the trajetories, in ...
11
votes
1answer
2k views

Join based on maximum overlap in PostGIS/PostGresQL?

I have two sets of polygons in two tables. The sets overlap each other. For each polygon in set A, I would like to get the ID of the polygon in set B that it overlaps the most. I'm using PostgreSQL ...
11
votes
2answers
3k views

Why are date vaules displaying a trailing 'Z' when layers are published with Geoserver and a PostGIS data store?

I have published various layers using Geoserver 2.2.x with a PostGIS 2.0.1 (PostgreSQL 9.1.x) data store. Fields that only have date values and not time/timestamp related values are stored as date. ...
11
votes
2answers
3k views

How do you display related records in QGIS?

Coming from an esri background I have always used relationship classes in a gdb to display records related to a feature. Eg. Relate 1 survey point to many documents, photos etc. Does any one know how ...
11
votes
1answer
1k views

Batch load multiple shapefiles to Postgis

According to the shp2pgsql help, the options drop, append, create and prepare are mutually exclusive. So, if I want to create a table from a shape and then append multiple other shapefiles, I do ...
11
votes
1answer
175 views

Visualizing index build?

What kind of visualizations/statistics can I get from PostGIS' GiST index? If we look at Wikipedia's R-tree entry, there are different R-tree variations and visualizations of bounding boxes of each ...
11
votes
1answer
493 views

How to speed up simultaneous working with PostGIS layers in QGIS?

we are working on one big layer at the same time (10-20 people). Some of us use QGIS 2.8.1 and the other 2.6. The user with 2.8 have a lot of crashes (QGIS). The 2.6 users don't have this crashes but ...
11
votes
1answer
948 views

Merge adjacent polygons and return all (adjacent and not adjacent) as Multipolygon?

I have a postgres database with administrative boundaries and their geometry. Each of those boundaries have an ident number. What I want to achieve: First I want to select all rows which are ...
10
votes
2answers
3k views

ST_Distance doesn't use index for spatial query

I can't get PostGIS 2.1 running on PostgreSQL 9.3.5 to use a spatial index even for the simplest queries. The whole dataset is 8 million points (population count grid from here). The table is created ...
10
votes
5answers
6k views

How to split OSM roads into individual segments at intersections?

I want to create a road-network for use with pgRouting using OpenStreetMap data. I loaded a shapefile from GeoFabrik into a Postgres table (with PostGIS enabled). However, one problem I had was that ...
10
votes
3answers
4k views

Selecting only Spatial tables from PostgreSQL database?

My database contains both Spatial and Non-Spatial tables, but I want to retrieve only Spatial table from the query. Any suggestions to select only Spatial tables. 'the_geom' is the geometry column in ...
10
votes
4answers
6k views

What does the “service” box in the PostGIS connection dialog stand for?

Can sоmеbody explain me what to write in the "service" box when connecting to the local DB. QGIS Lisboa and Postgres are installed.
10
votes
4answers
53k views

GROUP BY timestamp interval 10 minutes PostgreSQL [closed]

I need to group by a period of time spliting up by 10 minutes. I have this query below that returns me a result set: SELECT timestamp FROM table_1, table_2_shape_polygon WHERE ST_Within(...
10
votes
4answers
3k views

Creating Vector MBTiles?

I am searching for a tool to generate vector-tiles that I can then display using the Mapbox Map-View. We currently have a postgres database with all of our data. What is the best tool to generate ...
10
votes
2answers
638 views

Finding PostGIS tables that are missing indexes?

Recently I was looking around in pg_stat_user_tables and was surprised to see high numbers of sequential scans on some of my spatial tables. Sure enough, these tables were missing spatial indexes. ...
10
votes
2answers
11k views

How to perform SQL queries and get results from QGIS python console?

I'm writing a python script using QGIS API. I have to get results from postgres database table. The table has geometry field and other non-geometry fields. I want to perform SQL queries on non-...
10
votes
5answers
4k views

Split lines into non-overlapping subsets based on points

Given a table with line geometry, and one or more points that are snapped to this line in a separate table, I would like to split each line with one or more intersecting points at each of the ...
10
votes
2answers
2k views

Update database row with points that fall within polygons

I have a PostGIS/Postgresql DB that has two tables in it. One with point geometries and the other that represents country boundaries as polygons. I would like to add the country name that each point ...
10
votes
1answer
8k views

Postgis 2.0 - public.geometry_columns view does not contain correct info for views

We have recently updated our database to Postgis 2.0. In our system we used to create views for tables containing a geometry column. After creating the view we also manually updated the ...
10
votes
2answers
1k views

How to catch PostgreSQL/PostGIS errors in GDAL/OGR Python bindings?

Is it possible to catch PostgreSQL output error messages in OGR after a query has failed? For example conn.ExecuteSQL('SELECT * FROM non_existing_table;') In Postgres this returns an error message: ...
10
votes
2answers
2k views

Cutting linestrings with points?

I was checking the best way to cut linestrings by points. The scenario is: lots of streets, need the segments cut by intersect points, kind of this: I got linestrings (full uncuted by points) ...
10
votes
1answer
602 views

Compute parallel lines along a center line in PostGIS

I have street (road center line) and building polygon tables in my PostgreSQL database. The sample scenario is as follows: Problem: I need to compute parallel lines along street at the intersection ...
9
votes
1answer
11k views

How to create a table from existing tables (applying new schemas) in postgis DB?

I have a table that is geospatially enabled; that contain world borders. I want to break it down to different schemas (global, europe etc..) . My thought is to use use something that looks like this: ...
9
votes
4answers
3k views

Rename postGIS table and updating the GeoServer?

I want to rename one of my postGIS tables. However, when I do this by applying a simple ALTER TABLE old_name RENAME TO new_name; The spatial data, as I understand it, isn't updated with the new name....
9
votes
1answer
2k views

ST_Union fails with TopologyException despite valid polygons and using ST_SnapToGrid

I am working with Postgres 9.6 and PostGIS 2.2.2. These are my tables: Table "public.allparcels" Column | Type | ...
9
votes
1answer
4k views

How do I perform a proximity search with Postgis?

I downloaded the Geonames database (cities1000) and wrote a small Ruby program to import them into my table (geo_cities). I then added geography column called geog. Then I converted all of the lat/...
9
votes
2answers
5k views

Install PostGIS and TIGER Data in Ubuntu 12.04

Could someone write a brief albeit dumbed down idiot's guide to installing postgis and loading national Tiger data on ubuntu? I've tried a few guides, namely, http://wiki.bitnami.com/@api/deki/pages/...
9
votes
1answer
7k views

How to USE ST_difference AND ST_intersection in case of multipolygons? (PostGIS)

I'm trying to use ST_difference and ST_intersection with two layers of multipolygons. I want something like: With the input on the left and the result on the right. MY DATA: t1.geom contains 1000 ...
9
votes
1answer
2k views

Performance in calculating raster statistics in PostGIS

I'm trying to calculate raster statistics (min, max, mean) for each polygon in a vector layer using PostgreSQL/PostGIS. This GIS.SE answer describes how to do this, by calculating the intersection ...

1
2
3 4 5
64