PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

2
votes
1answer
20 views

Paste a feature from an existing shapefile into a row with null geometry in QGIS

If I have an existing row in my PostGIS database with NULL geometry, is it possible to select a feature from another layer in QGIS, and paste that into the empty geometry column for the PostGIS row?
5
votes
2answers
105 views

How to use ST_Difference to create linestrings from non-intersecting polygon boundaries?

There are two tables with polygon features in my PostgreSQL database. I want to create linestring features where the boundries of the polygons are not intersecting. CREATE TABLE schema.poly_a ( gid ...
1
vote
0answers
18 views

update query with st_intersects() is not finding results

So I am trying to identify which items from the requests table fall into some particular district from the district table using postgis. Basically, I am writing an UPDATE query that will take the ...
1
vote
0answers
11 views

OSM2PO Postgres to graph for A*

I have created a directed graph from the nodes stored within my Postgres db but the resulting graph doesn't seem to be fully connected and as a result my shortest path algorithm is not able to find a ...
2
votes
5answers
150 views

Create Statewide Hydrologic Soil Group Map

This may be better for Engineering? Not sure. Background: Previously I have downloaded SSURGO soil data for the state of Pennsylvania by downloading each county and merging the shapefiles. I then ...
2
votes
0answers
13 views

TopoGeometry commands with postgres are really slow

After messing around quite a while, I was finally able to get a topogeometry converted from my geometry table following the example of toTopoGeom. I have the topology extension, with a topology schema ...
3
votes
1answer
57 views

Strange behavior of of ST_Intersects in PostGIS

I'm quite new to GIS and PostGIS and, probably, this question has been already answered multiple times, but unfortunately I was not able to find an answer. I have few polygons and I want to find ...
6
votes
0answers
26 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 ...
2
votes
0answers
17 views

Create network graph from contiguous polygons in KML, or PostGIS?

I am using Postgres 9.5 and PostGIS. I have a table with a geometry field. This table has about 200 rows, in each of which the geometry field contains a Polygon. I would now like to derive an ...
1
vote
1answer
22 views

Syntax error in a query with ST_AsText and ST_CENTROID

I have a table in a PostGIS database where with this query : SELECT ST_CENTROID(pol8.geom) AS geom FROM polygon8 AS pol8; I got this: geom ...
-2
votes
0answers
22 views

how can i add tiled layer on Sharpmap using postgresql? [on hold]

map = new Map(); try { //VectorLayer v = new VectorLayer("Point", new PostGIS("server=localhost;port=5433;user=postgres;pwd=9839757437;database=OSM3", ...
2
votes
1answer
17 views

Multi-string data-set without z layer, applying ordinal values as a workaround

I'm new to GIS and have been learning a lot over the last few weeks, however, I've finally come to a point where I could use some help. I'm working with the pdx_streets data-set from ...
0
votes
0answers
19 views

Export image from PL/R on postgres

I'm trying to export an TIFF image from a PL/R Function in my PostgreSQL database. The output of the image appear be corrupted, because when I'll open the image, is showing the message: "Loading ...
2
votes
0answers
39 views

How to clean the postgres instance while setting cartoDB locally in ubuntu

I am following https://github.com/danseely/cartodb-install/blob/master/DEV-INSTALLATION.md for setting cartoDB locally in ubuntu12.04. While installing cartoDB-postgresql, the second install check ...
0
votes
0answers
20 views

Installing Osm2Pgrouting on debian?

I'm new to Postgis. I'm trying to install osm2pgrouting on debian, but when I run cmake -H. -Bbuild I get an error : Could not find PostgreSQL (missing: PostgreSQL_TYPE_INCLUDE_DIR) (found version ...
1
vote
1answer
21 views

Issue with GeoJson data format fetching from PostgresQL

I am trying to display the GIS data on the Google map using PostgreSQL. I have few Shapefiles, I imported those shapefile into PostgreSQL. Now I am trying to get this data using this query. After ...
2
votes
1answer
36 views

Finding route by joining next points

I have points data, e.g. p1,p5,p3,p6,p7 are the points of road-A, while p2,p4,p8,p9 are point of road-B. In database (PostgreSQL) points are in p1,p2,p3,p4,p5,p6,p7... How can I make a route, by ...
2
votes
1answer
31 views

trouble restoring dump, $libdir/postgis-2.1 missing

I have a university project in which I continue a project of last year. I never used those tool and I'm getting the following error: ERREUR: n'a pas pu accéder au fichier « $libdir/postgis-2.1 » : ...
3
votes
2answers
2k views

calculating percent area of intersection in where clause

I have a table of polygons (census block groups) in postgres. I want to tag each block group with the town (another polygon table) it mainly resides within. Is this possible? I'm thinking I'd need to ...
0
votes
2answers
243 views

Shp2pgsql - exec() not allowed on external server

Me and my collegue are using the shp2pgsql and psql commands in php code (a Drupal module to be precise). We put these commands in a php variable and use the exec() command to execute them. This ...
1
vote
1answer
16 views

PostGIS update data with ST_Tile

I need to update tiled raster data from the "whole image". I have created this query: UPDATE data_tiled AS t SET raw_data = f.raw_data FROM ( SELECT data_time, data_type, ST_Tile(raw_data, 250, ...
4
votes
1answer
68 views

Inconsistent results in ST_Distance

I have been playing with the ST_Distance function available within PostGIS and to my surprise, I have encountered some rather strange behavior... In the simplest setting, I have just two tables N and ...
2
votes
1answer
294 views

How to enable SFCGAL in PostGIS?

I have the problem with the enabling extension SFCGAL. CREATE EXTENSION postgis_sfcgal; doesn't work for me even I have the PostGIS v2.2. When I do that, it throws an error ERROR: could not ...
0
votes
0answers
34 views

ArcGIS Union equivalent in PostgeSQL?

I am using pgAdmin (v.1.22) for my postgres(9.5). I am still fairly new to postgres. I am trying to perform a union(intersection) of multiple layers where there is both complete and no overlap between ...
3
votes
1answer
100 views

Update area column after changes in QGIS

I'am using QGIS and POSTGIS, for example to automatically calculate the area of a new created polygon. I do that with following code: CREATE OR REPLACE FUNCTION calc_area() RETURNS trigger AS $BODY$ ...
1
vote
0answers
26 views

Identify Geographic features by clicking on published map (ArcGIS Server) [closed]

I have a published map on ArcGIS Server. My map is accessing traffic signals data from a table in PostgreSQL. I am trying to display feature properties by clicking on any traffic light. Solved!
1
vote
2answers
41 views

What PostgreSQL version to use with QGIS 2.8 (on Linux/Fedora 21)

I'm looking to install QGIS on my Linux/Fedora 21 laptop. QGIS ver. 2.8 seems to be the new/stable version (Feb. 2016) What PostgreSQL version should I use with that?
1
vote
1answer
68 views

ogr2ogr Shapefiles to Postgres SRS problem

I am trying to import to my Postgresql (9.1+129ubuntu1) + PostGIS (1.5.3-2) database a Shapefile with polygons with the ogr2ogr tool from Gdal-Bin package (1.7.3-6ubuntu3). I know the origin SRS of ...
3
votes
1answer
166 views

GROUP BY on geography in postgres 9.4.0 returns wrong/different results

table definition: CREATE TABLE search ( id bigserial NOT NULL, location geography(Point,4326) NOT NULL, location_aggregated geography(Point,4326) NOT NULL, created timestamp with time zone ...
2
votes
1answer
193 views

Basic sp-gist indexing?

Please forgive a very, very elementary question. I am using a PostgreSQL 9.3.5 database, and one of the tables stores longitude and latitude. (These are two separate columns.) I would like to do ...
2
votes
1answer
54 views

Checking if Point is inside a GeoJson polygon using PostGIS?

I have a list of points stored in my database, in a text format. lat | long "-73.88110000007934", "40.817699999744555" "-73.92675000057572","40.81840599962764" "-73.883573184", "40.751662187" I ...
0
votes
0answers
37 views

Modifying an algorithm in pgRouting or creating a new routing algorithm [PostGIS using OSM Data]

I am currently making a routing application that uses pgRouting, PostGIS and OSM Data. Everything works fine but I discovered that the Dijkstra algorithm in pgRouting should be tweaked a bit to be ...
0
votes
1answer
54 views

QGIS - what is the core database system? [closed]

What is the core database system used by QGIS? When you launch an existing project that has multiple .shp files (with associated .dbf tables), is the core db system used to convert that data to its ...
0
votes
1answer
59 views

How to improve PostGIS raster query

I have this query SELECT data_type, data_time, model_name, ST_Value(raw_data, 1, 202,417) AS val_0 FROM data WHERE data_type = 'type_a' AND data_time >= '2015-12-06 00:00:00' AND ...
1
vote
1answer
51 views

pgrouting tsp (travelling salesman problem) performance

I currently use a web API to solve TSP tasks. However my provider limits the query to a maximum of 150 stops. I therefore consider to host my own routing service based on OSM data and pgrouting. My ...
1
vote
2answers
41 views

Importing OSM data into PostGIS and display into QGIS

I am new to all the GIS thing but 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 with ...
2
votes
1answer
68 views

Raster statistics for polygon areas PostGIS

I'm trying to get zonal raster statistics for each polygon area. I have used the following method from this site: SELECT bufid, (ST_SummaryStatsAgg(ST_Clip(rast, geom, true))).* FROM rasttable, ...
1
vote
1answer
29 views

What projection to use in POSTGIS for LAT/LONG and 'contains' queries

I would like to store latitude / longitude information in a POSTGIS database. I will need to query that data with simple contains query (for display on a map, so the bounding will be simple box) and ...
1
vote
1answer
181 views

Utilizing OSM Data in a Postgres/PostGIS database in ArcMap

I've been trying for over a week now, and I'm not sure what i'm doing wrong and I haven't found the answer yet. I am trying to display and symbolize the OSM dataset over Jordan in ArcGIS for Desktop ...
0
votes
0answers
30 views

Mix Public Transportation data with pgRouting

I'm looking to mix 3rd party data, such as public transportation (routes, schedule is optional), with geographic data imported from shapefile/osm. With this I plan to give users an option to select a ...
1
vote
0answers
28 views

how to highlight the points within a buffer radius

i have created 1km radius buffer within that buffer few school points will come.how to highlight those points and how to retrieve that school data from database am using postgresql for db connection.
1
vote
0answers
18 views

Problems using pgr_fromAtoB - postgresql error

I have some trouble with the pgr_fromAtoB function of the workshop. I get: ERROR: column "ways" does not exist But "ways" is the table, not a column. What am I do wrong? My table ways looks like: ...
1
vote
1answer
18 views

postgis create a new layer from the st_within query for points with another layer

I am trying to create a layer that contains all of the businesses in Los Angeles proper. I have a shapefile for the polyon of the LA boundaries (shp_laboundaries) and I have a set of points in the la ...
1
vote
0answers
32 views

PostGIS query: filter rows by area?

I am working with PostGIS (in CartoDB). I want to find all rows in the database with an area of over 100 square metres. I've been trying this: SELECT *, ST_AREA(the_geom::geography) AS area ...
4
votes
2answers
4k 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 ...
6
votes
4answers
4k 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 ...
2
votes
0answers
35 views

How can I convert a shapefile from a single line segment to separate side segments using PostGIS?

I have a shapefile (STC) which is a single line segment per street with information for both the odd and even sides of the street. This .shp file has to be converted to separate side segments. To do ...
8
votes
5answers
3k 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 ...
2
votes
1answer
48 views

Fastest way to process OSM

I've been working with imposm and Python, it's fairly easy to parallelise. I've hit a performance problem when trying to associate the coordinates to the ways. Creating a dictionary from ...
0
votes
0answers
26 views

Unable to install pgrouting for postgres 9.3.5/postgis 2.1.7

I am running postgres 9.3.5 and postgis 2.1.7 on Ubuntu Server 14.10. I am trying to install pgrouting, but having no luck. 1) I tried the following based in the official workshop documentation: ...