PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

2
votes
1answer
40 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
290 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
72 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
4k 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
945 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
772 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
141 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
191 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
48 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
53 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
114 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 ...
6
votes
1answer
81 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 ...
5
votes
2answers
8k 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
68 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
74 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
25 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
84 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
257 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
46 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
77 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
934 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 ...
2
votes
1answer
59 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
71 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
85 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
160 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
74 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 ...
0
votes
1answer
100 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
108 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
193 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
310 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
90 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 ...
0
votes
0answers
52 views

Is it possible to convert osm2postgres table to pgrouting with osm2po?

I have already imported osm files with osm2psql in postgres. I would like to know if it's possible to convert osm data from this postgres table to pgrouting directly in the database with osm2po? Or ...
5
votes
3answers
467 views

GIN/GiST Full Text searches through openlayers, geoserver and postgres

I have a a web app that automatically maps recent tweets and allows you to search for certain keywords. it uses Postgres for the database, geoserver as the server, and openlayers as the cartographic ...
1
vote
0answers
40 views

QGIS as Spatial Database RAD or Hierarchical Attribute Viewer/Editor?

I have designed a geological observational spatial database in PostgreSQL/PostGIS to manage geochemical samples, QAQC and geological map objects. Now is the not so fun/easy part of designing a front ...
1
vote
1answer
35 views

Help using ST_Buffer for points

So I am trying to do a where clause on a select and my code is ST_Within(shape::GEOMETRY, ST_Buffer(ST_GeogFromText('@Value(_location)'), 5, 'endcap=round join=round') I am trying to buffer the ...
2
votes
3answers
529 views

PgRouting routing algorithm from start to end via nodes with restrictions

I have the following situation: I want to find the shortest route from A to B through nodes (C,D,E, etc.) with turn directions and road type filtering. Is there a way to accomplish this using ...
0
votes
0answers
35 views

Creating .OSM file from custom location data in database [duplicate]

I have the location data of a city loaded in my PostgreSQL database in a custom format. I would like to export this data into a .osm file adhering to OSM standards. I tried exporting it as shapefile ...
1
vote
1answer
68 views

PostGIS table structure question

I have been using QGIS to insert point data accompanied by information for each point into PostGIS. When I open PGAdmin to take a look at the table rows I noticed a column near the start of each row ...
3
votes
1answer
108 views

Update table based on closest point between its geometry(Point) and another table's geometry (MultiLinestring)

I think my question is rather simple, but being new to PostGIS, I can't figure it out myself nor being able to transfer knowledge of other questions to my issue. The problem: I have two tables ...
13
votes
3answers
680 views

How fast should I expect PostGIS to geocode well-formatted addresses?

How fast should I expect PostGIS to geocode well-formatted addresses? I've installed PostgreSQL 9.3.7 and PostGIS 2.1.7, loaded the nation data and all states data but have found geocoding to be much ...
2
votes
1answer
215 views

Postgis and python parameterised queries

I have a python script that i am trying to run in order to dump a postgres table and I noticed there is a pgsql2shp command in bash to perform this easily but I would like to incoperate the same ...
3
votes
1answer
1k views

How to use pgsql2shp when PostGIS extensions installed in a separate schema as geometry

I have the PostGIS extension (postgis functions and spatial_ref_sys table) added to a specific schema called postgis. I'm wanting to run the pgsql2shp utility with a select query on geometries stored ...
0
votes
0answers
130 views

OpenStreetMap Road and Polygon Data as 1x1 Degree Shapefiles

I am working on a global project for which I want to use OSM data as a model input. I am looking for advice on the best way to produce 1x1 degree WGS84 shapefiles from either regional extracts or the ...
2
votes
1answer
63 views

Generating Shapefiles from PostGIS as a Plugin

I used to generate shapefile from PostgreSQL database that is installed with PostGIS tables as templates (geometry_columns and spatial_ref_sys) Shapefiles generated just fine using the following ...
1
vote
0answers
170 views

PGSQL2SHP Commnad switch column type

I work with Postgis. I have table CREATE TABLE centerline ( gid serial NOT NULL, l_from integer, l_to integer, r_add_pre character varying(15), r_to integer ... } Export this table with ...
0
votes
1answer
57 views

pgsql2shp error: table does not exist

I am exporting tables from Postgresql 9.2 to shapefiles using postgis 2.0. I have the same system on other machines and it works correctly. I have tried running the command from a prompt and get an ...
1
vote
1answer
1k views

pgsql2shp error when query is introduced

I'm trying to export a table from PostGIS using pgsql2shp. The basic query to export the entire table works fine using the following format: "C:\Program Files\PostgreSQL\9.1\bin\pgsql2shp.exe" -f ...
1
vote
1answer
118 views

Couldn't Excute command line PostGIS pgsql2shp

I am using postgres 9.3 with postgis 2.1 on Ubuntu 14. When I tried to dump pgsql table using pgsql2shp, I get the following error. Error: pg_wrapper: pgsql2shp was not found in ...