Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
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
710 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
351 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
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 : ...
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
4answers
22k views

Importing .shp to PostgreSQL?

I want to import a shp file to postgreSQL. First I create sql file and then run PostgreSQL. To create sql file, I run this command in windows cmd: shp2pgsql -s 4326 worldCountries.shp worldcountries ...
12
votes
1answer
12k 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=...
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
12k 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 ...
11
votes
4answers
14k 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, ...
11
votes
1answer
17k 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
2answers
10k 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?
11
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....
11
votes
2answers
1k 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
900 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
472 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
892 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
11k 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 ...
10
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 ...
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
5k 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
46k 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
2answers
529 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
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 ...
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
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 ...
10
votes
3answers
2k views

Uploading Geopackage contents to PostgreSQL

As a bit of background, we are looking to transition away from the use of shapefiles. We've already started using Geopackages for desktop work (QGIS / ArcGIS) but often have a requirement to upload ...
10
votes
1answer
7k 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
534 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 ...
10
votes
1answer
141 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 ...
10
votes
0answers
702 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 ...
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
5k 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.
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
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
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
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 ...
9
votes
3answers
554 views

Why doesn't pgRouting return the best path?

Let the next portion of a graph: When I use the function shortest_path between the points A and B, I got the blue path. Why does this happen?
9
votes
1answer
174 views

How to get the contributors on OpenStreetMap

I have put my osm file in base thanks to osm2pgsql and I am looking for a way to get the contributor list. I have searched in my database and haven't found so much... and the osm wiki about the ...
9
votes
4answers
9k views

How to fix performance problem in PostGIS ST_Intersects?

I'm a newbie in postgis and I have a problem in query performance. This my query: SELECT DISTINCT ON (userid) userid ,ST_AsText(position), timestamp FROM table1 WHERE ST_Intersects ( ...
9
votes
2answers
1k views

How do I calculate Manhattan distance with PostGIS?

I am using the ST_Distance function to calculate the distance between two geometries (a train station and a building) Since I know that all buildings and all train stations are in Chicago, which has ...
9
votes
2answers
399 views

Using postgis to generate building shades

I am currently working on a solar potential estimation tool for existing buildings. The idea is to use buildings (=Polygon) shape, their height, and create the resulting shade at a given hour. I am ...