PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

2
votes
1answer
19 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 » : ...
1
vote
1answer
12 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, ...
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
61 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 ...
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
58 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
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!
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
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 ...
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
1answer
58 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
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 ...
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 ...
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
1answer
49 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 ...
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 ...
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
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 ...
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 ...
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, ...
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 ...
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
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 ...
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 ...
1
vote
1answer
24 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 ...
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 ...
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 ...
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 ...
1
vote
1answer
44 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 ...
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
0answers
22 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 ...
5
votes
2answers
74 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 ...
0
votes
0answers
30 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 ...
0
votes
0answers
20 views

Can updates be made to “ogr_fdw” foreign tables in PostgreSQL?

I am accessing a Microsoft SQL Server table (w/ SQL Server a geometry column) via an ogr_fdw foreign table. SELECT statements work great, including the PostGIS functions that I've tried. Are UPDATES ...
2
votes
1answer
32 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 ...
4
votes
0answers
30 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 ...
1
vote
1answer
31 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 ...
1
vote
0answers
23 views

pgrouting pointAtoB function

Has anyone successfully implemented pgr_fromAtoB function? Sometimes I get 0 results from points in the same line string, sometimes I do get results, How can accuracy be improved?, or maybe do I ...
6
votes
1answer
64 views

Geometry not displaying correctly in QGIS 2.12

I loaded a few Shapefiles from different sources into a PostgreSQL database, when I visualize them in QQGIS some of them are not shown. This how i added the geometry column in my tables: SELECT ...
3
votes
3answers
79 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 ...
0
votes
1answer
45 views

Can't execute a simple psycopg2 script twice when running from ArcGIS Desktop as a script tool

I'm building a geoprocessing service that queries an SDE database (running on postgres) and returns results. I do a lot of development by calling the script from a toolbox in ArcGIS desktop 10.3. ...
0
votes
0answers
46 views

Select points greater than a certain distance from related points based on shared foreign key id

I have a PostGIS-enabled PostrgeSQL db, using QGIS, with data using EPSG:4326, and projected as EPSG:29903. I have a table with points with pk point_id and a table with images with fk point_id. I want ...
0
votes
0answers
19 views

Appending Roads with Snapping [closed]

I have two different roads shapefiles. There are roads present in my second shapefile which are not present in first shapefile. I want to append all those roads from second into first one but i want ...
2
votes
0answers
40 views

display shapefile from postgres using arcmap?

I have uploaded shapefile using postgis extension in postgres but when i tried to display the shapefile in arcmap, it wont display the data in vector. this is the image of my connection. i followed ...
3
votes
1answer
41 views

Force ST_collect to return only a GeometryCollection

In the postgis documentation it is mentioned that ST_collect returns a MULTI* or a GEOMETRYCOLLECTION. But how can I force the function to only return a GeometryCollection not depending on it's input ...
1
vote
1answer
26 views

Use Postgis to convert wkb_geometry data type to geom datatype

How would I write an ALTER statement or otherwise to convert an existing imported data column from " wkb_geometry geometry(LineString,4326)" to a geom data type? I ask because I've moved to ...
0
votes
1answer
22 views

Tilemill Error - Zoom level 16

I am having an error in tilemill with postgresql and the Openstreetmap-carto style that I am having trouble figureing out the reason for. Everything is working perfectly up until I reach zoom level ...
1
vote
1answer
65 views

Nearest Neighbour function

I'm trying to create a function which calculates the nearest neighbors under a certain length in a table s_1 an stores them in an output table. This is what I've got so far: CREATE OR REPLACE ...