PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

0
votes
2answers
279 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
24 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
78 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 ...
0
votes
0answers
42 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 ...
2
votes
0answers
36 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!
2
votes
2answers
67 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
106 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 ...
2
votes
1answer
246 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
85 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
58 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
64 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
69 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
80 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
63 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
79 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
39 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 ...
0
votes
0answers
45 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
39 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
25 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
37 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 ...
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 ...
2
votes
0answers
47 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
52 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
41 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: ...
1
vote
1answer
823 views

Display data from geoserver on click map with openlayers

I tried to display data with popup from Geoserver or Postgres to my map, but it dosen't work at all. Can anyone please help me? I used this code: function Info(e) { if ...
1
vote
2answers
21 views

Insert query result to table (PostgreSQL)

This Query: ALTER TABLE planet_osm_line ADD vertices FLOAT; INSERT INTO planet_osm_line(vertices) SELECT st_NPoints(way) FROM planet_osm_line; does result in a empty column (NULL). What am I doing ...
6
votes
1answer
608 views

How can I make a PostGIS ST_Distance_sphere query more efficient?

Within PostgreSQL/PostGIS I have written a SQL query to get the closest distance for each element within a point coordinate table from a polyline table. The query comes back and works correctly, but ...
1
vote
1answer
54 views

Syntax error at or near “1” — PostGIS in Action — Chapter 11 SQL data dump

I'm following along with PostGIS in Action, 2nd ed., chapter 11, while attempting the SQL dump from the data files and I'm getting the following error: ERROR: syntax error at or near "1" LINE 189: 1 ...
3
votes
1answer
83 views

How to cast geometry to varbinary in PostGIS / PostgreSQL

In MSSQL Server, casting geometry to varbinary(max) produces a string that looks like this: 0x3D0B0000010CA0F3BE3B5CE9474100EC414E3AF93941 When you insert that string into a geometry column, a ...
2
votes
0answers
36 views

How to display join query results on a sublayer in CartoDB.js application?

I'm working with my first CartoDB.js application. I created a visualization on the app using cartodb.createVis('map', visjson_url, opts); The app has two layers, base map and data layer. the data ...
1
vote
1answer
250 views

Ogr2ogr - shape to postgis - is there a way to use and preserve an existing primary key?

I've been struggling with this for a while, tried a few different ideas inspired by these pages: http://www.gdal.org/ogr2ogr.html http://www.gdal.org/ogr/drv_pg.html ...
1
vote
1answer
169 views

SQL query works in PostgreSQL but not in GeoServer SQL View

I can't find a solution for this problem after a couple of hours of research. The query is: SELECT searchaddress('some string'); FETCH ALL IN mycursor; The function searchaddress returns a ...
1
vote
0answers
39 views

QGIS crashes when using PostgreSQL/PostGIS triggers

When using PostgreSQL/PostGIS triggers saving layer edits gets slower and slower every time when saving a layer edit. This issue occurs after a few up to a few dozen saved layer edits. Finally QGIS ...
1
vote
1answer
59 views

Why is raster data in range 0-255 in PostGIS?

I'm having trouble interpreting some data I've loaded into a PostGIS database. I scraped rasters from here and converted to sql files using raster2pgsql. I'm now using PostGIS's special functions to ...
6
votes
2answers
160 views

How can I cluster all points in a postgis table together based on their distance from each other?

I have a PostGIS (in postgres 9.3) table which has Points. I want to figure out the "clusters" in the data. If 2 points are < X distance apart, they should be added to a cluster, and this should be ...
4
votes
0answers
33 views

Wrong coordinates after loading OSM to postgre

After loading OSM data for whole Europe to postgres (Osm2pgsql) I'm trying to add them to Geoserver, but the coordinates are totally wrong. The SRS is correct (4326), but the coordinate values are ...
-4
votes
2answers
184 views

Creating spatial queries in QGIS? [closed]

How can I create spatial queries in QGIS?
2
votes
1answer
38 views

Select a subset of a PostGis table

I'm using a big table imported by a *.shp file of the city of San Diego. When I run pgr_dijkstra it takes more or less 5000ms, even for small distances. I was thinking, in order to improve ...
1
vote
1answer
286 views

Using database tables with bigint columns in ArcMap query layer?

I am very new to GIS and am using the ArcMap 10 program. Currently, I am trying to set up a query layer from a remote database. I am able to connect to the database, but I am having trouble making ...
0
votes
0answers
60 views

How to “INSERT” geotiff raster into postgresql/postgis table using psycopg2

Can someone walk me through how to insert a raster into a prebuilt postgresql table? The table has the postgis extension and I've been able to manually insert a geotiff using raster2pgsql, but I would ...
6
votes
4answers
3k views

Percentage of polygon in one shapefile within polygon of another

I'm a newbie, apologies if this is obvious/has already been asked and answered but I couldn't find anything. I have two shapefiles: 1. an administrative boundary layer for a county in the UK known as ...
2
votes
1answer
857 views

ERROR 999999: Error executing function - Failed to execute (CopyRaster)

ERROR 999999: Error executing function Unexpected operation No spatial reference exists This is a fragment from my log report: ERROR 999999: Error executing function. Database user ...
2
votes
1answer
761 views

Create isochrone from multiple start points

I want to create an isochrone. For this I could use the pgr_drivingDistance function with pgRouting (http://docs.pgrouting.org/dev/src/driving_distance/doc/index.html) but it only takes one start ...
7
votes
1answer
139 views

Create a Shapefile from SQL dump file

I am trying to map data by querying a table called xyz. I need to show the the_geom data on the map. I have below query in my mapserver .map file: DATA "the_geom from xyz using unique id using ...
2
votes
2answers
186 views

Merging contour lines along tile boundaries

I am working with the CANVEC data set. The data in canvec is broken up in to mapsheets. eg. 116B04. I have data covering the entire province of the Yukon. I have merged all the data in to one ...
1
vote
1answer
42 views

Given a lat/long how to find the nearest node in a route calculated by pgr_dijkstra?

I'm creating a table route_<route_id> for the route given by pgr_dijkstra, like this. In my application, I have a websocket with a mobile client that it's sending it's current position every x ...
2
votes
1answer
50 views

PostgreSQL default values in QGIS 2.8.2

I think the issue outlined in this question PostgreSQL default values in QGIS 2.8.1 and ticket #12391 is not fully solved yet. Negative numeric default values, and dates are not used by QGIS 2.8.2 ...
3
votes
3answers
100 views

Importing OSM files to PostGIS

I have installed QGIS 2.4.0 on Windows. I also downloaded an OSM file form Geofrabrik (SouthAmerica). I have been searching an option (plugin also) in QGIS for importing the OSM file to my PostGIS ...