PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

1
vote
1answer
1k views

Create closest point on multilinestring to use in shortest_path()

I am working a routing based application where I need to create a route between two points of interest. The problem which I am facing occurs after calculating network topology using ...
15
votes
1answer
480 views

How to 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 ...
13
votes
6answers
11k 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 ...
12
votes
2answers
11k 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 ...
11
votes
2answers
3k 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 ...
9
votes
1answer
737 views

How to view a 155 MB raster stored in PostGIS?

I am using the following command to load a raster into a postgres database: raster2pgsql -I -C -e -Y -F -s 3086 -I -C -M myraster.tif myraster -F -t 30x30 | psql -U postgres -d database -h localhost ...
8
votes
2answers
277 views

Find headwater polygons

This is a follow up question to this question. I have a river network (multiline) and some drainage polygons (see picture below). My goal is to select only the headwater polygons (green). With ...
8
votes
1answer
586 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 ...
8
votes
1answer
937 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 ...
6
votes
3answers
385 views

What is the easiest one click install method for PostGIS and it's entire dependencies?

For someone new to database servers and their dependencies, what is the easiest assured way of installing PostGIS on a machine with no other software beyond what comes with its operating system? I'd ...
6
votes
1answer
5k 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 ...
5
votes
3answers
5k views

What's the fastest way to do a bounding box query in PostGIS

I have a Postgres table, with almost 2 million rows, with a long-lat coordinates field in the form POINT(-73.4938 33.2405). Supposing there's a geospatial index on that field, what's the most ...
5
votes
2answers
1k views

How to detect duplicates and keep only one?

I've got a table with lots of points (200.000). Some of them are duplicates (not exactly but e.g. a metro station has eight entrances) and I want to keep only one of these metro entrances. I am able ...
5
votes
2answers
2k views

Which spatial DBMS should I select?

I found there are a number of RDMS have the spatial capability e.g. mysql, postgreSQL I considering which I should go forward! I would like to store the lat lon coord from the tracker to the db ...
3
votes
1answer
947 views

How to edit PostgreSQL geodatabase table using ArcPy?

I'm trying to update a table using a python script and it's not working. The table resides in a postgres geodatabase and is versioned. I have also verified my user permissions (I'm the admin). The ...
8
votes
2answers
4k 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 ...
8
votes
2answers
2k views

Is there workaround for very slow ArcSDE 10.1 data display

I'm suffering from very slow viewing data of and ArcSDE database from an Arcgis engine application, the SDE database is in localhost so there's no network issues. but i still don't know the reasons ...
8
votes
3answers
413 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?
8
votes
2answers
796 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 ...
6
votes
1answer
343 views

TileMill fails to load Europe data from PostGIS. What am I doing wrong?

The Problem: Using OSM-Bright in combination with an imported (osm2psql) Europe shapefile, tiles won't load unless I have 2-3 layers active (of the total of 29). I don't have this problem with an ...
6
votes
2answers
491 views

Do large vertex counts cause performance issues?

I have a table of several thousand tuples - each tuple has a geometry column. The problem is, these geometries have a large vertex count. The max vertex count is 400, while the average is 200. ...
6
votes
1answer
878 views

Data visualization and maintenance tool

Background Looking to leverage an existing GIS system for exploring organizational data. Architecture The following figure represents a high-level overview of the system's desired features: The ...
6
votes
3answers
2k views

How to load OSM data into PostGIS preserving all Tags?

I am new to the field of GIS and am trying to view source tags in OSM. I have downloaded the shapefiles from http://downloads.cloudmade.com and have loaded them into Postgres. When I view the data, I ...
5
votes
3answers
6k 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 ( ...
5
votes
1answer
654 views

Promotion radius in miles around venues using PostGIS

Bear with me as I'm new to PostGIS and GIS in general. I also don't know the domain well enough to create a good title for this post! Here is what I'm trying to accomplish: I have Venues in my ...
4
votes
2answers
2k views

Can POSTGIS 2.0 be install with POSTGRES 9.2 on Ubuntu 12.04 OS at the moment?

This question is more about environment set up. At the current moment, March 4th 2013, can POSTGIS2.0 be install with POSTGRES 9.2? I check their website out and to my understanding it is not ...
4
votes
1answer
1k views

raster2pgsql: Strange error when creating a table

Hi GIS Stackexchange people, When I try to load a asc file into my psql setup, I get a strange error message. I have created the sql input file via raster2pgsql -s 4236 -I -C -M gis_data.asc -F ...
4
votes
1answer
2k views

Error creating a spatial database. ERROR : could not load library “/usr/pgsql-9.1/lib/rtpostgis-2.0.so”

I am running PostgreSQL 9.1 with Postgis 2.0 on Fedora 15. When trying to install the raster support, psql -d mydb -f rtpostgis.sql -v ON_ERROR_STOP=1 I get the following error ...
4
votes
2answers
1k views

Merging polygons that intersect by more than a specified amount

I am working with a dataset that contains a subset of property boundaries in a local government area, thus my dataset is made up of several thousand properties but not all are touching. Since it is a ...
3
votes
1answer
112 views

Adding vertices to polygon in PostGIS?

For example I have rectangular polygon (4 vertices). I want to add mid-point on each line of the polygon, so in total now I have 8 vertices. And from them I want to build new polygon who will have ...
3
votes
2answers
537 views

Creating polygons from line segments using PostgreSQL and PostGIS

Is there a PostGIS function that creates polygons from multiple line segments? I want to convert the pictured thirteen lines to four polygons. All lines belong to the table "boundary" (gid, geom). ...
3
votes
2answers
2k views

How to edit a PostGIS database using ArcGIS for Desktop Add-In/Extension? (no ArcSDE)

I need to improve an existing solution to be able to do multiuser editing within a PostGIS database from ArcMap. Available for the solution is ArcGIS for Desktop 10.3 [Basic]. (No non-free Extensions ...
3
votes
1answer
361 views

PostGIS - ST_Within or ST_Disjoint performance issues

I have a table of about 150,000 points in PostGIS with a spatial index and a SRID of 27700 (OS). I want to select the points that fall outside England and Wales. I have a multipolygon table with ...
3
votes
1answer
732 views

Drawing isochrones on map?

I am a fourth year graduation student of the Amsterdam University of Applied Sciences. I am currently working on my final thesis for which I am doing research on the Amsterdam Airport catchment area. ...
3
votes
1answer
139 views

Add vertices costs to postgresql routable network

In osm2po it's possible to create custom routers as the sample router TrafficSignalsRouter, but the costs seem to be added on vertices after the routing. Is it possible to add these costs on the ...
3
votes
1answer
331 views

st_clip and gdal_translate: “Error browsing database for PostGIS Raster properties” - Why?

Im using shell-script, postgis2.0 and gdal for trying to clip raster apart, using an overlapping raster and a the_geom-object. At first I create a view to clip the overlapping part of the raster. It ...
3
votes
2answers
3k views

PostGIS Error: type “geography” does not exist

I just upgraded my development machine to Windows 7 and am therefore reinstalling PostgreSQL and PostGIS. I used the Postgres Plus installer, as recommended on the Postgres website, and followed this ...
2
votes
2answers
195 views

Merging linestrings in QGIS using PostgreSQL and PostGIS

I've to merge all linestrings from a PostgreSQL table "river" where not more than two start-/endpoints are overlapping. In the illustration below I use a "M" to mark all linestrings that needs to be ...
2
votes
3answers
1k views

Configuring SFCGAL to PostGIS Db

I was using PostgreSQL with PostGIS and Pgrouting. Recently I needed to install SFCGAL for some extra functions for my PostGIS db. After installing SFCGAL, now I don't know how to update my PostGIS db ...
2
votes
1answer
292 views

Update postgres/postgis point data attribute from another enclosed polygon shapefile in QGIS

I have a placename point table in postgis with 'district' column. a districts polygon shapefile with district name in 'Name' column. See the image below. I would like to update the 'district' ...
2
votes
0answers
423 views

How do I catch PostGIS/PostgreSQL RAISE messages in QGIS?

I have a trigger set up in PostGIS/PostgreSQL which will show a RAISE WARNING when an inserted or updated polygon is within a certain distance of some predefined planning constraints. I want this ...
2
votes
1answer
355 views

Monitoring Geoserver for Production Environment

Is there any opensource server monitoring is available for Geoserver ? Like watchdog and nagios? Suppose if geoserver is stopped working whether mapmeter will automatically start it..Like the ...
2
votes
2answers
4k views

Problem when calculating longitude and latitude for points

I have a PostGIS database with SRID=900913 (Google Mercator) and I would like to know the latitude and longitude of points. I use this command: ST_X(ST_TRANSFORM(the_geom,4283)) Following what is ...
1
vote
0answers
126 views

GeoDjango: AttributeError: function 'GDALAllRegister' not found [closed]

Im trying to get started with GeoDjango. I am using this https://docs.djangoproject.com/en/1.8/ref/contrib/gis/tutorial/ tutorial. When I run "python manage.py migrate" This error occur: ...
1
vote
5answers
3k views

Unable to install the PostGIS extension to PostgreSQL on Ubuntu 12.04 LTS

I just installed Linux Ubuntu 12.04 LTS (on a virtual machine). I then successfully installed the PostgreSQL 8.4 database management system. I am now trying to install PostGIS, which is an extension ...
1
vote
2answers
4k views

Converting mulitlinestring to linestring in PostgreSQL with PostGIS? [duplicate]

I am using postgres with postgis and I have a set of points which I would like to interpolate with a set of lines. Now my problem is that the set of lines are of type multilinestring. Is there a ...
0
votes
0answers
72 views

Is there any feature limit for Spatial data migration to Postgre SQL9.2…?

I am trying to migrate spatial data to Postgre SQL9.2 using 'Postgis Shapefile & DBF Loader 2.0'....Each time it returns a "Runtime error'. Primary data extension - .shp ; Data type - ...
7
votes
4answers
5k 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 ...
6
votes
2answers
535 views

Find start of river

Edit: Follow-up question here (= Find headwater polygons). How can I determine the start of a river in PostGIS? I have a river network (Multiline) and want to find the startpoints of the rivers. ...
6
votes
2answers
3k views

Most reliable way to import large dataset with osm2psql

I know my question is similar to slow import via osm2pgsql to postgresql database and Optimizing osm2pgsql imports for OSM data but as we currently have quite some problems importing a large set of ...