PostgreSQL is an open source object-relational database system.
40
votes
7answers
4k views
Best practices for PostGIS production environments?
What PostGIS documentation have you come across that has been helpful when tuning PostGIS for a production environment?
I would like to get together with my DBA to setup a Postgresql/PostGIS ...
32
votes
6answers
10k 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 ...
24
votes
2answers
10k views
How to change the SRID of exisisting data in PostGIS?
While importing my shapefile data to PostGIS, I did not select the proper Projection.
How do I now change the SRID of the data, without transforming the Coordinates?
24
votes
5answers
9k 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 ...
21
votes
4answers
18k 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 ...
18
votes
3answers
4k 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 ...
17
votes
3answers
3k 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 ...
16
votes
1answer
186 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 ...
15
votes
2answers
2k views
Mixing geometry types in one PostGIS table
I've 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 "lid" field ...
14
votes
2answers
3k 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 ...
14
votes
1answer
380 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 ...
14
votes
1answer
2k views
How to publish PostGIS Rasters in Geoserver?
What is the best solution to 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. ...
13
votes
2answers
32k views
How to insert a point into postgis?
I have created one table in my Postgis. But I can not insert point. I do not know what's wrong with my query?
CREATE TABLE app (
p_id INTEGER PRIMARY KEY
);
SELECT ...
13
votes
6answers
13k 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 ...
13
votes
6answers
8k 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 ...
13
votes
2answers
2k 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 ...
12
votes
4answers
481 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
3answers
944 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 ...
12
votes
4answers
8k 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 ...
11
votes
3answers
4k views
Which is the best way of 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, but I have this question: I have a PostGIS database with (obviously) GIS data, If I want to make statistical ...
11
votes
5answers
3k 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 ...
11
votes
5answers
2k 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 ...
11
votes
1answer
588 views
Nearest Neighbor Grouped Selection in QGIS
OK - so I have an interesting problem I am trying to solve. 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 ...
10
votes
2answers
2k views
Postgres/Postgis spatial index - no speed up
I have a spatial table in a postgres/postgis database. Every row in it represents a Polygon. It is of following form:
+----+--------+
|gid | way |
+----+--------+
|241 | 01030..|
The geometric ...
10
votes
2answers
9k 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 ...
10
votes
1answer
1k 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 ...
10
votes
1answer
612 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, ...
10
votes
2answers
4k 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 ...
10
votes
4answers
843 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 ...
10
votes
2answers
7k 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 ...
10
votes
2answers
2k 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 ...
9
votes
1answer
153 views
How to get the contributors on Open Street Map
I have put my osm file in base thanks to osm2pgsql and I am looking 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 osm2pgsql ...
9
votes
2answers
2k 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. ...
9
votes
1answer
383 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 ...
9
votes
2answers
472 views
Best design for Open Source Python / PostGIS prototype
I am writing a data intensive web application that is delivered through apache. My question is about how to best arrange processing given that there are multiple options.
I have at my disposal ...
9
votes
1answer
587 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
3answers
559 views
How to select only Spatial tables from the postgres 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 ...
8
votes
1answer
4k 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:
...
8
votes
3answers
5k views
Error when trying to run Create Extension postgis
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 ...
8
votes
1answer
607 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,
...
8
votes
3answers
339 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
361 views
PostGIS- GROUP BY for continuous data values?
In the sample code for the PostGIS ST_ConvexHull function, multiple polygons would be created based upon different, discrete values found in the field "disease_type".
--Get estimate of infected area ...
8
votes
2answers
2k views
ArcGIS 10.1 edit PostgreSQL layer
I've been working with QuantumGIS/PostgreSQL/postGIS for a while now, without problems. But now I have to change QuantumGIS for ArcGIS 10.1.
I'm having trouble adding a layer from PostgreSQL and ...
8
votes
2answers
1k 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
2answers
555 views
PostGIS Intermittent INDEX Performance
I have a table containing approximately 55 million data points ( point is a geometry with SRID 4326 ) and for my query I need to join this to an area table ( currently have ~1800 areas ) which ...
8
votes
1answer
959 views
PostGIS - Calculate progressive distance between points on a GPX path
I am attempting to build an elevation profile using PostGIS-PostgreSQL only.
I have imported GPX data in my PostGis database, so now I have a table full of the GPS coordinates of the track and the ...
8
votes
1answer
195 views
What spatially-aware GUI administration softwares exist for PostgreSQL/PostGIS (free or paid)?
Most of my experience with PostgreSQL has been PGAdminIII and psql, but I'm interested to know what others use. Is there PostgreSQL equivalent to SQL Server Management Studio (SSMS) and its ability ...
8
votes
1answer
2k 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 ...
8
votes
2answers
738 views
What are the Pros and Cons of hstore data type in postgis?
PostgreSQL has a module named hstore that implement the use of key/value pairs (it's something like the OpenStreetMap model, but in a single table).
What are the Pros and Cons of hstore regarding GIS ...
7
votes
2answers
4k 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 ...
