PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

1
vote
0answers
8 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, ...
3
votes
1answer
57 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
282 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
31 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
89 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
24 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
37 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
55 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
165 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
189 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
50 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
29 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
56 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
48 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
40 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 ...
1
vote
0answers
28 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
67 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
28 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
175 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
28 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 ...
0
votes
0answers
26 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
17 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
15 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
31 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
33 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
25 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
737 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
1answer
173 views

how to fix this ERROR: Start vertex was not found

I am attempting to make an SQL selection but receive an error. My statement: SELECT * FROM network JOIN (SELECT * FROM shortest_path(' SELECT gid AS id, start_id::int4 AS source, end_id::int4 ...
1
vote
2answers
17 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
558 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
23 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 ...
8
votes
1answer
207 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 ...
3
votes
1answer
49 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 ...
4
votes
1answer
194 views

Error installing PGRouting database extensions

Im trying to install pgRouting and I cannot reach the end of the way because of the following error: psql:/home/user/share/postgresql/contrib/pgrouting-2.0/pgrouting.sql:2459: ERROR: could not load ...
2
votes
0answers
26 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
212 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 ...
0
votes
1answer
222 views

How do you unregister a table from a esri geodatabase?

How do you reverse the "Register with Geodatabase" operation? I reckon it isn't uncommon to accidentally register (since it is the first option on Manage menu in ArcCatalog 10.2). Is there a tool ...
3
votes
2answers
819 views

Debugging ogr2ogr “AddGeometryColumn failed” and “Terminating translation prematurely after failed translation of layer”

As noted in the title, I'm getting the following error: ERROR 1: AddGeometryColumn failed for layer pretty_polys, layer creation has failed. ERROR 1: Terminating translation prematurely after failed ...
3
votes
1answer
243 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
1answer
168 views

St_OffSetCurve with self-crossing lines

I have about 50,000 linestrings in PostGIS. They represent routes along roads. But I want them to be (roughly) pavements (aka sidewalks) instead. So I need to move all the lines 3 metres to the left ...
5
votes
1answer
94 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
2answers
301 views

adding geometries to a source, target table for multiple rows in pgrouting

I am having an issue trying to perform the pgr_dijkstra algorithm and getting back a line in my table for multiple rows. I have a table that has a columns of source, target node id's but I cannot ...
1
vote
0answers
11 views

Server Doesn't Listen Error when connecting to PostgreSQL from Client [migrated]

I have done a lot of research through the Google and have not been able to com up with a solution. I installed PostgreSQL 9.5 and PostGIS 2.1.8 On Ubunutu Server 14.04 and configured PostgreSQL as per ...
0
votes
1answer
632 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
732 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 ...