PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

0
votes
1answer
734 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 ...
1
vote
1answer
145 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
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 ...
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
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
1answer
210 views

Google Streetview Images in infowindow

I have followed the instructions on this blog post on adding streetview images to the infowindow: http://blog.cartodb.com/a-picture-is-worth-1-000-attributes-infowindows-with/ I can do it without a ...
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 ...
-4
votes
2answers
167 views

Creating spatial queries in QGIS? [closed]

How can I create spatial queries in QGIS?
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 ...
1
vote
1answer
275 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
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
18 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 ...
4
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 ...
3
votes
1answer
576 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
728 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 ...
1
vote
1answer
555 views

PG::UndefinedFunction: ERROR: operator does not exist: point = unknown

Recently, I developed a feature to save and fetch points(lat and lon) from DB. It is working fine on my local, but on production database it is giving error: PG::UndefinedFunction: ERROR: operator ...
7
votes
1answer
133 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
174 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
2answers
328 views

Getting OSM maps in Equirectangular Projection via osm2pgsql

I'm trying to import some OSM data into psql as equirectangular projection using osm2pgsql with the following command: osm2pgsql -c -d osm -S /usr/local/share/osm2pgsql/default.style -l ...
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 ...
2
votes
1answer
44 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
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 ...
2
votes
2answers
3k views

Formatting SQL queries for use in Arcpy?

I have to make an SQL query on a column having not adequat name "type_borne_id", I couldn't make SQL queries using arcpy.Select_analysis neither selectbyattribute, but when I use select_analysis ...
2
votes
1answer
77 views

Load style from database in QGIS

Maybe it's a stupid question, but I don't know how to connect layer style with database in QGIS. When I save the style to postgres database in QGIS, it automatically saves the style in the table ...
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 ...
4
votes
2answers
6k views

How to get the data type of each column from a postgis table

I need to get the column data types of all columns in a table, including the geometry types. What I wanted to know is if there is a function or SQL that gives something like this: column_name | ...
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
1answer
30 views

GeoServer Sql view setup error?

The problem goes like this: I am trying to route osm data with the help of this tutorial: (http://workshop.pgrouting.org) but in the step where I create the sql view and add the parameters it fails to ...
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
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 ...
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
1answer
70 views

Problem with MapServer WMS

Below code is showing the addition of mapserver file in openlayers 3, it is successfully showing me image in the browser but when i zoom that image it does not zoom. It comes back to originial view. ...
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 ...
2
votes
1answer
200 views

Calculate road distance between GPS points

I am working on tracking system that track location of vehicles on the map and tell their total distance from the beginning till today. I have device_id, timestamp and GPS location in the table like ...
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 ...
1
vote
0answers
55 views

OSM data import error on Windows 2012 server using osm2pgsql

I am trying to use OSM data using mapserver (ms4w) on windows. Set up all tools and downloaded data for this purpose. http://download.geofabrik.de/north-america/us/georgia-latest.osm.bz2 Extracted ...
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 ...
10
votes
2answers
628 views

Update database row with points that fall within polygons

I have a PostGIS/Postgresql DB that has two tables in it. One with point geometries and the other that represents country boundaries as polygons. I would like to add the country name that each point ...
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 ...
4
votes
2answers
57 views

Get the 2 points immediately before and after a given point in PostGIS

In the picture below: The given point is C, I would like to select A and B, but not Z (which is closer to C than B but it's not immediately after in the LineString). Note that C is always ON the line ...
2
votes
2answers
63 views

ST_Distance loop

I want to create a postgresql function which calculates the distances between random points who are stored in one table. For the Table: CREATE TABLE s_1( id bigserial primary key, the_geom geometry, ...
1
vote
1answer
117 views

Postgis ST_Distance query returning inaccurate results

I am using the following query to retrieve records from a postgresql DB @services = Service.select("*, ST_Distance(services.lon_lat, ST_GeomFromText('POINT (lat lon)', 4326)) as ...
1
vote
0answers
63 views

Optimal SDE Database Configuration [closed]

I am starting to set up PostgreSQL as a DBMS for ESRI SDE instances. I am looking for recommendations for setting up the roles/schemas for this. My architecture is: Virtual private server with ...
4
votes
2answers
398 views

PostGIS: ST_Equals false when ST_Intersection=100% of geometry?

I have 2 datasets consisting of cadastral parcel data - roughly 125,000 rows each. The geometry column is WKB polygons representing parcel boundaries; all data are geometrically valid (the polygons ...
0
votes
1answer
31 views

Restoring public.layer_styles table from PostGIS dump

I created a PostGIS database on a PostgreSQL server and stored some QGIS styles (in public.layer_styles). When dumping and restoring the database only this table gives me an error and stays empty. I ...
4
votes
1answer
71 views

How to create trigger to check geometry validity?

I want to create trigger to check geometry validity in spatial table. I have a table: CREATE TABLE vec_geometry ( id bigserial is_valid boolean, invalid_reason character(128), geom ...
5
votes
2answers
114 views

Remote connection to PostGIS?

I have recently setup PostgreSQL and PostGIS on a debian server on a local network in my home, which is working perfectly. I am able to access the db from other machines on the same network using the ...
1
vote
2answers
105 views

libiconv-2.dll missing when running shp2pgsql

My system is Windows 7 with PostgreSQL 9.4 and PostGIS 2.2. I'm running shp2pgsql program from command line (cmd window, not PSQL interface). shp2pgsql "D:\file.shp" schema.table > file.sql I'm ...
3
votes
1answer
81 views

after truncating a table in postgres, why does qgis lose track of updates/features?

I'm running a python script which drip-feeds data slowly into a postgres database with postgis extension. I'm using autocommit, and committing one row at a time. Horrendously slow, but I need to do it ...