PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

0
votes
1answer
1k views

How do I explode a MultiLineString using ST_Dump() in PostGIS?

I want to do what this blog post does: explode a MultiLineString into individual LineStrings. I'm using CartoDB but don't get individual LineStrings as the result of my query. I still get a ...
0
votes
0answers
16 views

Where to use the shp2pgsql commands?

I need to upload some very large tables (over 10Gb) into Postgis so I actually found this link What is the best hack for importing large datasets into PostGIS? The DB manager is far too slow and only ...
0
votes
1answer
18 views

How to edit a table in order to count the number of points in the same location?

Using QGIS, I have a point shapefile that has multiple points with same coordinates across different locations. I need a count of those points. For example there are 15 features that have the exact ...
0
votes
2answers
19 views

QGIS error in merging large point shapefiles

While trying to merge 2 large point shapefiles using the merge shapefiles tool under the data management. These point files have over 20Gb and have the same coordinate system. Problem is, that the ...
1
vote
2answers
36 views

Show results of ST_Union in QGIS

Currently, I am working on SQL in PostgreSQL and I execute query of vector data by "ST_Union" function for the spatial data. Now, I want to access the result of query in QGIS 2.14, but I can't. Is ...
1
vote
0answers
14 views

How to add fields to postgis layer loaded in qgis using python

I have loaded a layer from Postgis in qgis and I am adding the fields to the layer using the follo code snippet: canvas = qgis.utils.iface.mapCanvas() aLayer = canvas.currentLayer() field_name = ...
1
vote
1answer
40 views

Creating shapes/lines from geo coordinates in postgis

Following the suggestion from this topic I'm posting my question here. I have a table in postgres/postgis with geo data and I want to show them on map via qgis. Here is the table: city | street ...
0
votes
3answers
62 views

How can i use WFS in Openlayers 3 with mapserver and PostGis?

I didn't find a documentation to display features ( vectors ) combining MapServer ( mapfile ) and PostgreSQL/PostGIS and OpenLayers 3 ?
3
votes
1answer
256 views

Is there any readily available implementation of Mapserver (or Geoserver)- Postgres-Openlayers combination?

I am developing an web GIS for map data presentation for public. Is there any readily available implementation of Mapserver (or Geoserver)- Postgres-Openlayers combination or similar? More Details: ...
1
vote
0answers
11 views

I am using an example fracnsites to make a similar map but I don't under stand the ajax code?

I am using an example fracnsites to make a similar map but I don't under stand the ajax code? The example has js code: var map, fields = ["gid", "createdby", "featname", "feattype", "status", ...
0
votes
0answers
13 views

QGIS not loading default layer style from PostgreSQL database on startup

I have saved a vector layer style to a PostgreSQL database. I then amended the layer_styles table record for the relevant layer by editing the styleqml field (just changed the outline_width value for ...
2
votes
2answers
107 views

Load style from database in QGIS

Maybe it's a stupid question, but I don't know how to connect layer style with database in QGIS. When I save the style to postgres database in QGIS, it automatically saves the style in the table ...
1
vote
0answers
40 views

Creating simple spatial database for loading GPX files? [on hold]

As an absolute beginner in this topic, I am trying to create a spatial database, and then import GPX files. I need it in order to parse those GPX files, and have tables such as "locations" or "stays" ...
0
votes
0answers
25 views

Problem with loop for pgr_Dijkstra

I try to use pgr_Dijkstra through Postgis and Pgadmin to find the shortest path in a network between a lot of nodes. So I thought I could to "automate" the process with a PLSQL query. Here is a query ...
0
votes
1answer
179 views

Issue using pgr_dijkstra from Postgres pgRouting

I am using Postgres with Postgis extension to retrieve streets (only for pedestrian use) and several "natural" points. I need to make updates every minute and i am working on a small bounding box. For ...
0
votes
1answer
30 views

ST_Union or ST_Collect on PostgreSQL union result?

Newbie to PostGIS/Postgres. I have the follow query which returns me 2 GEOMETRYCOLLECTIONS. Is there a way within my current query to return one polygon? SELECT ...
1
vote
1answer
675 views

PG::UndefinedFunction: ERROR: operator does not exist: point = unknown

Recently, I developed a feature to save and fetch points(lat and lon) from DB. It is working fine on my local, but on production database it is giving error: PG::UndefinedFunction: ERROR: operator ...
0
votes
1answer
42 views

Geocoding addresses based on own reference data

I have a historical addresses dataset in form: |Name |Description | Where "Name" is a single field with house number, street name, city and country. I have digitised two georeferenced ...
1
vote
2answers
77 views

Importing OSM data into PostGIS and display into QGIS

I am new to all things GIS! However, I need to set up a full GIS infrastructure for a project. I have installed a Postgres server with PostGIS. That is on ancient hardware for now, 32bits processor ...
0
votes
1answer
47 views

Lines from points in postgresql postgis

I have this table (all row aren't in order): time, id, latitude, longitude time is character varying and the format is: dd/mm/year hh:mm id, latitude and longitude are numeric I would create lines ...
0
votes
1answer
204 views

How to check overlap between two sets of Linestrings using postgis

I have 2 sets of LINESTRINGS, each set containing around 50 LINESTRINGS(which form a road), how do I check whether these two sets overlap or not ? Thanks in advance.
2
votes
1answer
55 views

Displaying data from a partitioned PostgreSQL / PostGIS table is unacceptably slow in QGIS

I have two very similar datasets of 27 million polygons stored in PostgreSQL / PostGIS. One is loaded as a single table, with spatial indexes and regular indexes on key fields. The other is ...
1
vote
1answer
36 views

PostGIS with clause not using spatial index

This query uses the spatial index and it's quite fast: explain select a.the_geom from chicago_major_streets a, stormevents_locations_2014 b where ...
0
votes
1answer
34 views

select neighbour polygons in sql

I have a database of few milion records (all houses in my country). Now I want to select all records who have a neighbouring record (a house next to it) and give this a status 'T'. Each record has a ...
5
votes
5answers
624 views

Improve performances on distance coverage routing - PostgreSQL

I have to create layers of land coverage from one point to anywhere around on a given street network for a given distance (ex: FROM house, DISTANCE 30000m) An important factor to understand; I need ...
0
votes
2answers
30 views

Not able to create postgres Sql file using Osm2po

I am newbie to the GIS community. After downloading the osm2po jar file, I have run following the instructions from underdark tutorial. Also, from Carsten's answer in gis stack exchange, I have ...
14
votes
1answer
976 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 ...
0
votes
0answers
19 views

PostGIS - raster coodinates - no result for negative values of GPS

I have data in raster and try to run this query WITH ref As ( SELECT (ST_SetSRID(ST_Point(-20.12, 51.50), 4326)) AS pt ) SELECT ST_Value(raw_data, 1, ref.pt) AS val_0 FROM model_data The problem ...
6
votes
1answer
107 views

Leveraging capabilities of PostGIS for large (ish) tables in PostgreSQL?

I am using PostgreSQL and PostGIS to do some exploratory analysis and eventually spatial analysis. I have a table with 40M+ points (geolocated tweets) in the United States that has a spatial index. I ...
1
vote
0answers
46 views

One way Replica deleted feature not synchronizing when I synchronize

I have created one-way replicas from 50 geodatabases to one geodatabase (Arcsde. With PostgSQL 9.2) After that, I update my feature class in parent geodatabase, but when I synchronize the replica the ...
0
votes
0answers
31 views

Loading GML data into Postgres with ogr2ogr

I'm working with a new OS dataset, Highways Network, trying to get it loaded into Postgres and set up in some kind of usable state. I've loaded all the data already, but it seems some elements of ...
3
votes
1answer
280 views

User locking postgres/postigs table without privileges

I have a PostgreSQL 8.4.4 database with PostGIS 1.4.2 and I am experiencing a problem where an external GIS system (using embedded CadCorp SIS 7.0) is connecting to the database and taking Access ...
0
votes
0answers
17 views

Maximum likelihood Between 2 linestrings in Postgre?

I created a lot of linestring datasets and loaded them all in PostgreSQL, I want to compare said linestrings 2 by 2 using a maximum likelihood Query which would give me a coefficient proportional to ...
1
vote
2answers
44 views

How does pgr_createTopology assign source and target?

This is the first time I post a question here. I was following the tutorial from Anita Graser http://anitagraser.com/2013/07/06/pgrouting-2-0-for-windows-quick-guide/ , on a roads table which ...
0
votes
0answers
15 views

Postgis load raster to DB running on AWS RDS with raster2pgsql

I want to perform very common task: I have Postgresql + Postgis running on Amazon Web Service cloud, using RDS service. I have there some vector layers and can execute SQL (Postgis) commands via ...
3
votes
1answer
53 views

How to use a polygon as a reverse cookie cutter on features in a Postgresql database

I want to be able to use a polygon in a database to remove all features and parts of features that falls within the polygon. Not as part of a query transferring the database to another database. I ...
3
votes
1answer
66 views

How to loop in PostGIS to add the next lat long to a route

I want to loop over the results of a query and then pass row(n)'s longitude, latitude and row(n+1)'s longitude and latitude to another function. I have a query which returns a set of coordinates from ...
1
vote
1answer
38 views

Postgres st_intersection points/polygon

I am trying to use the ST_Intersection function to intersect points by county and find the counts by county, but I am having no luck. SELECT counties.name, FROM counties INNER JOIN routes ON ...
1
vote
1answer
52 views

Postgres/PostGIS calculate area of Polygon in Square Miles

Polygon in Postgres/PostGIS database. It has the spatial reference 4269 which is in degrees. I know there is a ST_Area() to calculate it. this is my query to select the areas in the SRID units. ...
1
vote
0answers
23 views

Missing vector features when import from .sqlite to postgis?

I imported vector data from a .sqlite file to PostGIS but not all of my vector features imported. I haven't been able to determine a reason for most of the loss of features between the databases ...
0
votes
1answer
38 views

Python to Postgres insert Geometry syntax

Having trouble creating geom columns and inserting geometry lat lng values into postgres table Is my syntax correct in creating the point geometry fields? st_makepoint is the point constructor. ...
1
vote
1answer
50 views

Making selections from two tables based on the reference table in PostgreSQL/Postgis

Based on the address locations ad for the region of interest (as a reference), I am trying to make selections from two street layers street1 and street2. What I have tried is shown by the code below: ...
0
votes
2answers
31 views

Proper handling in PostgreSQL/PostGIS only after many queries?

I am trying to analyze GPS data of a parade on a fixed route with a certain variance due to the urban surrounding (canyoning). I'd like to project the GPS waypoints to the nearest points on the route ...
1
vote
1answer
23 views

Postgres/postgis st_makepoint

I am looping through a csv, taking latlng values and trying to add there geometry to postgis enabled postgres table. xy values are int's conn = psycopg2.connect("connected") cur = conn.cursor() ...
0
votes
1answer
25 views

lwgeom_to_geojson: 'PolyhedralSurface' geometry type not supported

I am using PostGIS (PostGIS 2.2.0 with SFCGAL extension) and I have data in postgres which is loaded using citygml2psql tool and it contains polyhedralSurface geometry. But when i run query : ...
-1
votes
0answers
18 views

Create a trigger for postgresql to populate a state field

We have a Postgresql SDE database that we would like to automatically populate certain fields in the attribute table when a new feature is added like State (based on location), user info and time. I ...
0
votes
0answers
18 views

Not able to edit PostGIS SDE featureclass in ArcGIS Standard

I have created an Enterprise geodatabase in PostgreSQL and now uploading OS MasterMap data using QGIS OS Translator Tool. The feature classes created are not editable, however if I create a blank ...
1
vote
2answers
739 views

ERROR: Operation on two geometries with different SRIDs

I'm new when it comes to GIS and PostgreSQL. I have searched everywhere for the cause of this problem but I can't find the solution. I was following the documentation here First i created a table: ...
0
votes
1answer
210 views

What is the difference between ST_GEOMETRY and WKT?

What is the difference between ST_GEOMETRY and WKT? Is ST_GEOMETRY just a database type and WKT (well-known text) is a textual representation of the data?
1
vote
0answers
17 views

Swapping start and end points of line (i.e. direction) of LineString in PostGIS?

I know that postgis has the function to find the start and end point of a line. Is there a function that can swap the start point and end point? (Note: I do not mean literally changing the ...