Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
9
votes
2answers
605 views

How to ST_Split features in one table by features in another?

I need to split polygons (layer 'pol') by both closed and non-closed linestrings (layer 'lin'). Unfortunately I don't get proper results running the following query. CREATE VIEW splitted_pol AS ...
9
votes
2answers
463 views

What spatially-aware GUI administration softwares exist for PostgreSQL/PostGIS?

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 ...
9
votes
1answer
4k views

Using PostgreSQL with QGIS and ArcGIS?

Is it possible to use PostgreSQL with QGIS and ArcGIS? i.e. one database for different clients. Is it anything that I have worry or any problems to use the DBMS from two different software? I know ...
9
votes
4answers
159 views

Limit Rows Through Spatial Function

I'm trying to improve performance for the query below. No matter how I write the query (subquery in FROM clause, subquery in WHERE clause) postgres insists on running all ~570K rows through the ...
9
votes
3answers
9k views

PostGIS ERROR: could not load library “/usr/lib/postgresql91/lib64/postgis-2.0.so”

OS is openSUSE 12.1, PostgreSQL - 9.1, PostGIS - 2.0. Postgre/PostGIS are installed from Application:Geo repository. After recent updates when I try to connect to my database I get the following ...
9
votes
2answers
582 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
163 views

Difference between SpatialJoin with “…where…” and “join…on…”

I wonder where the difference between these two spatial joins is and, if there is a difference, when to use which. SELECT * FROM points p, shapes s WHERE ST_Within(p.geom, s.geom); and SELECT * ...
9
votes
1answer
993 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 -...
9
votes
3answers
706 views

How to transform isolines to isopolygons with postgis?

I have a table a postgis table of isolines that's defined like this: CREATE TABLE myisolines ( gid serial NOT NULL, isotime timestamp without timezone, val numeric(10,4), geom geometry(...
9
votes
1answer
2k views

Intersection of multiple layers in PostGIS

I need to write PL/pgSQL stored procedure for calculation of intersection of multiple layers. For example, for three layers A, B, C this function should return ABC geometry: Function takes as input ...
9
votes
1answer
732 views

Use array[] datatype in QGIS from PostgreSQL

I'm just a beginner in GIS, so I'll be glad to see any comment here. I have tables in PostgreSQL which I've connected to QGIS. There are columns in tables with datatype array[]. But it's not easy for ...
9
votes
1answer
518 views

Is it possible to replicate an SDE geodatabase including the archive tables?

I have a database in one location which I would like to replicate to another using one-way replication. In testing we haven't been able to replicate the archive tables, which is unfortunate because we ...
9
votes
1answer
293 views

Compute visibility graph on sphere

I have a PostGIS table with some polygons (stored using the geography data type). They represent regions on a spherical earth. For each pair of vertices chosen from among all the polygons, I want to ...
8
votes
2answers
18k views

Transforming from SRID 900913 to 4326 with PostGis

I have two Postgres tables with different SRIDs: 4326 and 900913. I want to convert the latter to 4326. I first changed the SRID using: Select UpdateGeometrySRID('table', 'geomcolumn', 4326). I ...
8
votes
3answers
2k views

PostGIS: ST_Equals false when ST_Intersection=100% of geometry?

I have 2 datasets consisting of cadastral parcel data - roughly 125,000 rows each. The geometry column is WKB polygons representing parcel boundaries; all data are geometrically valid (the polygons ...
8
votes
6answers
7k views

Unable to connect Postgis from QGIS 1.8.0

I have installed PostgreSQL and PostGIS on my laptop (Win 7 64 bit OS). I am running QGIS 1.8.0 on my machine . When I am trying to connect Postgis I am getting connection failed error then I tried ...
8
votes
2answers
9k views

Importing large dataset with osm2psql? [closed]

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 OSM data ...
8
votes
2answers
3k views

Creating a spatial PostGIS table as a result of a PostGIS query

When I want to store the results of a spatial query as another PostGIS table, what should I do? I am interested in identifying all residential buildings that fall within a set of parcels: CREATE ...
8
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-...
8
votes
2answers
762 views

PostGIS: Approximating a house number from address range

I'm developing a reverse-geocoder for Canada. So far, given a lat/lng, I can find the nearest street (line segment), which includes line segment direction and address ranges for both sides of the ...
8
votes
2answers
2k views

Changing Buffer table geometry dynamically in PostGIS using plain SQL

I have two tables in PostGIS, a point and a point_buffer table. The point table has a buffer_distance field, with a default value say 200. Now I want to change the buffer table geometry whenever I ...
8
votes
2answers
599 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
1answer
3k views

How to create linestrings with a definite angle and length, that are fixed to a point

I want to create linestrings with a definite angle (e.g. 160°) and length (e.g. 2m) that are fixed to a number of points of another linestring. So, I want to use the ST_DumpPoints function to find the ...
8
votes
1answer
451 views

How can I optimize a Point in Polygon query for millions of points when most of the points lie within the polygon?

I have 150 million points in a point table and would like to find the few points lying outside a given polygon geometry. I know that 99.9% of the points are within the polygon geometry. I am ...
8
votes
2answers
2k views

How to generate one geojson file from several rows in Postgres/PostGIS?

I have a table with geospatial objects, in particular, polygons. I know that I can generate from one row a geoJSON string using ST_AsGeoJSON(http://www.postgis.org/docs/ST_AsGeoJSON.html). But I would ...
8
votes
3answers
7k views

How can I transfer data from one PostGIS database to another?

I have quite a bit of GIS data in a old PostGIS Database (version "1.1.6") and I need to transfer it to another PostGIS Database ("POSTGIS="1.5.3"). I tried creating a backup and restoring that on ...
8
votes
1answer
6k views

How to find the nearest point by using PostGIS function?

table A: lat | long | the_geom | code | sign 13.8433095 | 100.6360357 | 0101000020E61.... | ABC | start_point 13.7544738 | 100.5459646 | 0101000020E6..... | ABC | end_point 13.4124215 | 100.6232332 |...
8
votes
3answers
2k views

How to make use of indexes with inner join in Postgis?

I got 2 sets of points in 2 separate tables. Table_a got 100k points and table_b got 300k of points. I trying to find nearest points in relation find me any point from table_b that is within 50 meters ...
8
votes
3answers
4k views

Adding a GIS feature to existing database entry with QGIS

I've built a database with postgreSQL with tables, which include geometry columns. When I add some data to my non-geometry columns, I can see them in QGIS when I connect to my database using postgis. ...
8
votes
1answer
2k views

Are there limitations using a PostGIS out-db raster?

I have been trying to import a large raster into a PostGIS 2.0 database and hit a few stumbling blocks. Initially when I tried to import the raster using raster2pgsql, I would receive the following ...
8
votes
1answer
2k 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
485 views

Postgres/postgis cursors

I have a road network with StreetName and min/max address ranges for each segment (ie- StreetName = 'Main St', Min = 100, Max = 199). I also have individual address points with similar information (...
8
votes
1answer
3k views

PostGis vs Oracle Spatial 11g performance

I am having a performance issue updating a table in Oracle Spatial 11G while converting 63,000,000 points to WKT format. I also made a comparison with Postgres. Both Postgres and Oracle run in two ...
8
votes
1answer
1k views

Creating columns in PostGIS using osm2pgsql hstore tags

While I feel this may come down to a database question, it feels GIS-related enough that I feel comfortable posting it here. I'll be happy to take it elsewhere if it belongs there, though! I've ...
8
votes
3answers
1k 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 ...
8
votes
1answer
157 views

Can't load PostGIS partitionned table in QGIS

I partitionned a table (over 13 millions geographic point data) in my PostgreSQL database with this statement: CREATE TABLE data.data_fusion ( id BIGINT NOT NULL, source ...
8
votes
1answer
2k views

Calculating mean value of polygon from raster in PostGIS?

I started with a NetCDF .gri and .grd raster file of the UK provided by a colleague. I clipped it in R to be only London, exported and converted to a ASC file, and then imported it to PostGIS using ...
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
2answers
1k 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
2answers
4k views

Update/refresh edited PostGIS table in QGIS

I imported a PostGIS table (with geometry) from my PostgreSQL database as a layer in QGIS. Later, I edited the table using PostgreSQL UPDATE commands. Is there a way to quickly update (like a refresh ...
8
votes
2answers
374 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 John'...
8
votes
2answers
2k views

Where can I find PostGIS error codes?

Using ST_Contains in where clause sometimes gives me error messages like ERROR: GEOSContains: TopologyException: side location conflict at 37.3706 55.7882 For now I can't guarantee validity for ...
8
votes
1answer
8k views

How to import a raster into PostGIS?

I'm trying to follow the instructions here to load a raster into a PostGIS database: python raster2pgsql.py -s 4269 -I -r *.tif -F myschema.demelevation -o elev.sql I understand that I replace * ...
8
votes
4answers
2k views

Will PostgreSQL 9 work with ArcGIS 10?

I realize it is not listed as a supported Database. I am interested to see if anyone has tried to install and use it.
7
votes
2answers
3k 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). I'...
7
votes
2answers
252 views

Confused about PostGIS and Postgres terminology

I am documentating my stuff and not sure what is the right way to write this, "I have installed Postgres and added the shapefile to the Postgres database. This database is then PostGIS enabled" or "...
7
votes
3answers
9k views

Technical comparison of Oracle and PostgreSQL [closed]

I have searched on google and the archives for a technical comparison of Oracle and PostgreSQL (also MySQL), unsuccessfully. Is there any comparison that covers more than just the basic differences? ...
7
votes
2answers
15k views

How to connect to Postgres with QGIS?

Oddly, it should be simple, but I can't connect to a local PostGIS database. My installation is with Mac OS X (10.8), Postgres.app (in doc it's said to have PostGIS included) and Quantum GIS 1.8. ...
7
votes
2answers
2k views

Copy geometry data as HEXEWKB from csv to table in PostGIS

I have a table in PostGIS 2.3 (PostgreSQL 9.5, and pgadmin III) where I store Multipolygons. I have a csv which is a straight export from another database, including the geometry column in hexewkb ...
7
votes
2answers
7k views

ST_Transform doesn't work in PostgreSQL

When i run the query with ST_Tranform I get this error: Input geometry has unknown (0) SRID SQL state: XX000 How can I resolve this error? I want to convert from UTM into geographic lat/lon. ...