PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

3
votes
1answer
73 views

PostGIS multiple conditional statement on spatial join

I want to make a multiple conditioned spatial query in PostGIS 2.0/PostgreSQL 9.3., OS Windows 7. There are 2 tables, cadastre (Polygon) and roads (Linestring). Query: Select the sum (one number) of ...
2
votes
0answers
65 views

How to load GeoJSON into PostgreSQL database using Python?

What I have tried to do is (In Python IDLE): import psycopg2 import json try: conn = psycopg2.connect("dbname='carto' user='postgres' host= localhost password='password'") except: print "I am ...
6
votes
2answers
397 views

Find start of river

Edit: Follow-up question here (= Find headwater polygons). How can I determine the start of a river in PostGIS? I have a river network (Multiline) and want to find the startpoints of the rivers. ...
3
votes
0answers
80 views

Spatial query too slow to handle bigger OSM extracts

I'm trying to compute some statistics for OSM data using PostgreSQL 9.3.5 and PostGIS 2.1.4. I started with a small bavaria extract which I downloaded from Geofabrik. The db schema is the normal API ...
1
vote
1answer
44 views

How do I change the PostgreSQL data directory in the OpenGeo Suite 4.5?

I am using OpenGeo Suite and I would like to move the PostgreSQL databases files to a different drive than my operating system. The default location installed by OpenGeo Suite is in the ...
1
vote
1answer
59 views

QGIS 2.6 how to prevent user to drop a postgis table

Does anyone know how to prevent user to drop a postgis table? I have a vector layer imported from a postgis2.0 database, and one of my users drop it accidently. Fortunate I had a backup, but i don't ...
1
vote
1answer
31 views

Select Polygon Intersections Between Two Tables With No Common Key

I am new to geodatabase queries and cartodb. I am trying to write a SELECT query that will do the following. I have two tables. Table 1 contains congressional district polygons and info about ...
1
vote
1answer
20 views

SELECT geometry FROM spaces WHERE geometry ARE WITHIN OR OVERLAP polygons FROM OTHER table

I've got two tables, one containing LINESTRINGs and one containing POLYGONEs. I would like to select all the LINESTRINGs that are containing within the outer boundary of all the POLYGONs combined. ...
-4
votes
1answer
23 views

Make connection from Postgresql to QGIS? [closed]

I'm still new in QGIS. I want to add data from one layer to another layer in QGIS. How could I accomplish that?
1
vote
0answers
166 views

Using ArcMap 10.3 with PostgreSQL 9.3.x database and PostGIS 2.1.1?

I have a PostGIS database that we use for data storage. I would like to open this data in ArcMap instead of storing it in another format. In previous versions of ArcMap it has been possible to open ...
2
votes
4answers
85 views

Postgis don't use spacial-index with st_intersects

I imported the data from openstreetmap using osm2pgsql and copied it another table structure. If I made a query for containing rows using st_intersects, st_contain or someone else, postgis don't use ...
0
votes
2answers
48 views

Find closest points in routing network for a set of points to pick shortest route

I have the following WITH SQL Query which does select a set of filling stations along a route within the distance of 1000m. In the SQL statement I fetch 1 Point from the station and search the closest ...
0
votes
1answer
45 views

Get postal codes of a city in OSM via postgis/postgresql

So, I'm trying to get the postal codes of a city (polygon). Unfortunately, I never get consistent results with my code: SELECT a.name, b.tags->'postal_code' FROM planet_osm_polygon a JOIN ...
2
votes
1answer
39 views

am I setting up data from google geocoding correctly for ST_DWithin search?

Sorry to ask a basic question, but I have not been able to find this laid out entirely anywhere. I am mostly relying on this SO post: What Spatial Reference System do I store Google Map's Lat/Lng ...
1
vote
1answer
41 views

PostGIS returning a field one level higher in the Json

I have the following SQL on a PostGIS Database ($properties contains a list of fields, including osm_id) SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, ...
0
votes
1answer
32 views

Post and Reconcile buttons in versionig toolbar grayed out in Arcmap

I have a versioned GDB (Postgresql) and I have versions DEFAULT-> A -> B. Version A modifies two poly lines and version B modifies one of the two that is modified in version A. In ArcMap, even though ...
1
vote
1answer
55 views

How do I sum values that are geographically close to each other?

I have a dataset of Boko Harams rampage in Nigeria uploaded to CartoDB. I have columns like coordinates, dates, number of victims. Would it be possible to write a query that would sum the victims ...
0
votes
0answers
30 views

QGIS Crash: Enter Credentials

just wondering if anyone has come up against this issue...in the office QGIS will come up with an 'Enter Credentials' box with 'Error: Too many clients already'. When you enter your username and ...
1
vote
0answers
46 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 ...
5
votes
3answers
185 views

PostGis: Geometry from UTM text

I have plain data in a postgresql table which looks like this: ╔════╦═══════╦═════════════╦══════════════╦══════════╦═════════════════╗ ║ id ║ index ║ easting ║ northing ║ utm_zone ║ ...
2
votes
1answer
80 views

ST_INTERSECT of each element of two tables (polygons, lines)

Im new to postgis and trying to solve the following problem: I have two tables: 1. containing polygons 2. containing lines I need to find out the id of all polygons on each line and fill them in new ...
0
votes
1answer
38 views

Finding lines of similar attribute with respect to nodes connected to its both Ends

I have line networks with node generated for Start and End Point . how to check if a line has correct attribute with respect to point created at its End. for instance FuID_Line = ABC_BDQ and Start ...
0
votes
0answers
33 views

Routes through centroids and array functions

I have a table stopcentroids4 like in figure with the following fields: gid, routes, geom and i want to draw a line for each route that runs through centroids. In my code there is a mistake. ...
0
votes
0answers
24 views

ST_Touches Function in Iterative Process

Im a complete newbie to postgis. I have the following problem: Got two tables: "mp_power_lines_new" -> contains lines of an electricity grid "uw.new"-> contains polygons of all substations in this ...
1
vote
2answers
38 views

How can I add extensions to PostGIS?

As newbie with PostGIS, I downloaded Postgresapp for macosx. Everything works fine, but I noticed some differences with Stackbuilder which adds to extensions address_normalizer and grouting. Can I add ...
0
votes
1answer
27 views

How to transform isolines to isopolygons with postgis?

I have a table a postgis table of isolines that's defined like this: CREATE TABLE myisolines ( gid serial NOT NULL, isotime timestamp without timezone, val numeric(10,4), geom ...
1
vote
1answer
63 views

How to create postgresql triggers to calculate geometry from x,y and vice versa at the same time

I have a postgis table with point geometry which looks like this CREATE TABLE mypoints ( gid serial NOT NULL, coordid integer, geom geometry(Point,4326) ); And a table with coordinates ...
0
votes
1answer
57 views

ST_AsGeoJSON function slow in PostGIS

I'm currently trying to pull neighborhood data from Zillow's Neighborhood page (http://www.zillow.com/howto/api/neighborhood-boundaries.htm) and then load it onto a leaflet map, openstreetmap. I ...
1
vote
0answers
55 views

how to automatically update column based on the second layer, trigger, postgreSQL

I am newbie in postgreSQL. I have 2 tables, observation and landcover, i want to create a trigger which would allowed to update the column observation.land based on the spatial information according ...
0
votes
1answer
36 views

geometries with different SRIDs errors (even after setting SRID)

I've looked at existing questions about this (including this one) and followed the advice that was shared there. I'm still seeing this when I run some queries: RuntimeError: Postgis Plugin: PSQL ...
0
votes
0answers
70 views

box2d spatial type

I'm storing data that defines a box. I created a column by adding it to my table as follows: SELECT addgeometrycolumn('grid', 'grid_geometry', 32650, 'GEOMETRY', 2); I then insert my data in using ...
1
vote
1answer
37 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
47 views

PostGIS replace pixel value by lookup table?

I was wondering if it is possible to write a PostgreSQL statement to replace the value of each pixel in an input PostGIS-raster with a value obtained from a look up table. For example, suppose I have ...
4
votes
1answer
50 views

Finding all segments that makeup a road in OSM

I've spent 2 days trying to figure this out and I'm afraid that it's something that's not possible. If that's the case then I'm thinking the wrong with with respect to OSM data. Let's say I have a ...
1
vote
2answers
30 views

Learning pg_routing - shortest_path error

I'm learning PostGIS and pg_routing to help in our project planning and grant applications at my transportation agency (we wanted to move beyond the limitations of the 1/4 mile walking buffer we use ...
1
vote
0answers
63 views

Create GeoServer SQL View with variable number of arguments ( double precison [ ] )

I was trying to create a PostGIS procedural function which will perform a particular operation depending upon the user's provided coordinates. But since the user can provide any number of coordinates, ...
2
votes
0answers
63 views

How do you disconnect ArcGIS Pro from a PostgreSQL database?

What is the equivalent to the ArcCatalog's Disconnect option in ArcGIS Pro? I have successfully connected to a Postgresql database, but cannot figure out how to disconnect. This is what I'm seeing ...
3
votes
2answers
74 views

pgrouting - network distance many-to-one shortest paths

I want to find the distance (km) between each origin point to the closest destination point (restaurants) using pgrouting. I have 3 tables my road network with nodes and edges 140 origin points - ...
2
votes
0answers
37 views

filling the attributes according to intersection of two layers (Postgresql)

How to automatically fill attributes of a point layer according to the exact position in the polygon layer? I mean how to write a command in PostgreSQL to automatically fill the attributes by the ...
1
vote
0answers
27 views

how Npgsql.EntityFramework support Spatial data

I am using Npgsql.EntityFramework + EntityFramework6.My database is postgresql. I Added to my C# project with this tutorial : ...
2
votes
0answers
18 views

What are the problems of loading overlapping (or the same) tifs into postgis?

I am still early on the Postgis learning curve. I have an existing table in Postgres - it looks like this: create table "public"."mytable" ( "rid" serial primary key, "rast" raster, "filename" ...
2
votes
2answers
105 views

How to change from EPSG:900913 to EPSG:3857 in Postgresql

I didn't use the '-E 3857' switch when importing the OSM data into postgresql using osm2psql. I'm trying to set up a map server for the first time and I'm following the instructions from here: ...
1
vote
1answer
60 views

How to select geography features within a certain distance given in miles?

I am just taking the first steps in using POSTGIS, but having trouble finding a simple example. I have a database with a Key field, lat and lon fields and a GEOGRAPHY field. I just want to have a ...
2
votes
1answer
58 views

Is it possible to use spatial function to retrieve non-spatial data?

I'm using PostGIS in postgresql.I wish to calculate points within the radius from particular geocode.For that i have created a table with spatial column geocode having datatype as geometry and ...
3
votes
1answer
31 views

PostGIS 1.5 Errors with PostgreSQL 8.4

I am trying to add PostGIS functionality to a database that I created in PostgreSQL using the following command: psql -d <db_name> -f /location/to/this/file/postgis.sql However, I receive a ...
1
vote
0answers
25 views

How to Increase the length of column type geometry in postgres(pgAdminIII)

I am just wondering is there any way to increase the default length of column type geometry in postgres. Since we are trying to save large number of vertices polygon in to the geometry column geom in ...
0
votes
0answers
35 views

Any way to speed up my postgis topology creation?

I need to do topology aware simplification, and I'm investigating PostGIS's new topology extension. However it's taking a very long time to create the topology, and I'd like to know if there's ...
0
votes
0answers
32 views

Upload SHP to PostgreSQL via QGIS

There was the error when I try to upload the SHP to the PostGRESQL. The feature is the line type. The error was: ERROR: Geometry type (LineString) does not match column type (MultiLineString) Do ...
1
vote
0answers
88 views

Query layer in ArcGIS 10.2.2 with PostgreSQL not querying data

After some days I was finally able to import OSM data to a PostgreSQL database and connect it with ArcGIS 10.2.2 I can load every piece I want by simple clicking "Add data ..." however a SQL query ...
1
vote
2answers
47 views

How to get the geometry value from coordinates

I have a table with empty geometry column. I have two columns in the same table which has xcoordinate and ycoordinate values. I want to get the geometry value formed by these two coordinates. The ...