PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

9
votes
0answers
101 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
1answer
53 views

How to update a timestamp in SQL?

I've explored this forum fairly extensively trying to get answers as to why my SQL isn't working. I need help with the timestamp on a torque map. I've used the following formula, obviously changing ...
1
vote
1answer
46 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 ----------...
0
votes
0answers
48 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 out....
2
votes
0answers
45 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 ...
0
votes
0answers
36 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 "9....
1
vote
1answer
33 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
353 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
32 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
58 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 ...
4
votes
1answer
86 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
2answers
72 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
120 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
2answers
308 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$ ...
2
votes
0answers
38 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
73 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
71 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
177 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
100 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
99 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 ...
3
votes
1answer
42 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
63 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
57 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
110 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
53 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
65 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
47 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
35 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
96 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, ...
0
votes
0answers
71 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
28 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
56 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
104 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
44 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
152 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
1answer
70 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
2answers
151 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
45 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 ...
6
votes
2answers
254 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
95 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
1answer
82 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
42 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 performance,...
4
votes
0answers
35 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
58 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
29 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
86 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
151 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
77 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 ...
2
votes
0answers
47 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 ...