PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

2
votes
0answers
32 views

Error editing 'editable' view in QGIS

Taking some code from this post, I've been trying to create an 'editable' view in PostGIS. I'd like to store label placement properties in a separate table, build a view of the data I want to use for ...
0
votes
0answers
42 views

Timezone map including territorial waters

I have a project that requires timezone lookup for given coordinates. http://efele.net/maps/tz/world/ is an awesome resource however the polygons only cover land. Sydney Harbour is considered seas in ...
3
votes
1answer
353 views

Close linestring to linering in PostGis

is there a function which closes a linestring to a linering? I couldn't found such a function in the documentation.
4
votes
1answer
4k views

How to import QGIS Raster into PostGIS

I have created a IDW heatmap using QGIS 1.8.0 and need to import the raster into PostGIS 2.0.1. QGIS saves the raster layer as a .asc file. How do you import the raster into PostGIS as a Raster? What ...
0
votes
0answers
42 views

Read a vector layer from a Postgres/PostGIS database via a SQL query with QGIS and ogr in a python script

I have a treatment table for different aggregation stategies on a geospatial table in a PostgresSQL/PostGIS database. The aggregation strategies addressing temporal and species specific pre-selections ...
1
vote
1answer
188 views

shape file into DataSet in postGIS using openJUMP, but getting error

I am new to this field and I want to save my shape file into DataSet in postGIS using openJUMP, but I'm getting an error...can anybody tell me solution for this?
0
votes
1answer
59 views

Calculate centroid of a point cluster

Having this function that inserts locations in a database: def insertLocation(cur, label, point): print label cur.execute(""" SELECT label, centroid, point_cluster ...
1
vote
1answer
187 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 st_distance").order("...
1
vote
2answers
398 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 /...
9
votes
1answer
170 views

Compute visibility graph on sphere

I have a PostGIS table with some polygons (stored using the geography data type). They represent regions on a spherical earth. For each pair of vertices chosen from among all the polygons, I want to ...
0
votes
1answer
283 views

How to check overlap between two sets of Linestrings using postgis

I have 2 sets of LINESTRINGS, each set containing around 50 LINESTRINGS(which form a road), how do I check whether these two sets overlap or not ? Thanks in advance.
0
votes
1answer
28 views

Database access pattern using PostgreSQL and ArcGIS

I am planning to connect to a PostgreSQL DBs on a server from ArcGIS on my laptop. My server admin is interested to know "The access pattern: How, when, how often, (would ArcGIS access the DBs) during ...
0
votes
0answers
40 views

Crop raster files when importing with readGDAL from DB

I have quite a lot of raster files stored in a PostgreSQL DB. Each of them has around 200MB and is representing EU-wide data. However I only need the the rasters to cover the area of Germany. I'm ...
2
votes
1answer
51 views

PostgreSQL Shapefile import failed

I am very new to PostGIS/PostgreSQL. In fact, I downloaded it because of the answers I got to this question: Distance between centroid and farthest point of polygon I installed both PostgreSQL and ...
0
votes
0answers
41 views

postgis iterate over rows (cursors)

I need to repeat the same set of operations for each road polygon by using an id (road_id): I have tested my code only on one polygon of geometry using a series of functions inside a WITH query: ...
4
votes
1answer
210 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 geometry(...
0
votes
1answer
217 views

Issue using pgr_dijkstra from Postgres pgRouting

I am using Postgres with Postgis extension to retrieve streets (only for pedestrian use) and several "natural" points. I need to make updates every minute and i am working on a small bounding box. For ...
0
votes
1answer
1k 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
0answers
38 views

PostGis - st_union polygons, keep sum of attribute

There are multiple My current query is: SELECT (ST_Dump( ST_Union( way ) )).geom AS way, 10000 AS way_pixels FROM ( SELECT way, way_pixels, ...
2
votes
0answers
15 views

PostGIS - raster - bit operations

I have a raster data in the database in separate rows. I would like to select 2 rows and do a bit operation with them (like |, &) and store the result in memory as a query output. Is this possible?...
0
votes
1answer
37 views

PostGIS Shapefile and DBF Loader - automatically Linked dbf file

This is my system: Windows 7 Postgresql 9.5 PGADMIN 3 PostGIS Shapefile and DBF Loader plugin I have imported data from a DBF file correctly with the plugin. My problem: This DBF file changes ...
5
votes
1answer
86 views

How to include aggregate field values of clustered points

I am creating a map that has point features representing things like campgrounds, picnic areas, boat launches, etc. In cases where many points are clustered in an area (representing amenities for the ...
0
votes
0answers
13 views

osm2pgsql: bad result during COPY error

Hi Everyone so I am trying to import only the roads dataset into postgres. I have taken osm data from geofabrik regions and extracted roads from them using osmfilter them then combined them using ...
0
votes
1answer
34 views

How to calculate the start and end lat/lon of a linestring which is not a part of a ST_buffer?

I'm trying to find out the lat/lon of points on a linestring that are not contained inside a geometry formed by a ST_buffer. I can find if the linestring intersects the geometry but I'm not able to ...
1
vote
1answer
723 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 ...
1
vote
1answer
37 views

inserting points in postgis with pdo (php)

I have a problem with sql insert statement using PDO (php, postgis, postgres). I have tried to use different combinations, but none of them work. Here is what I ve got: $latitude= 46.07175; $...
0
votes
1answer
79 views

PostGIS - Calculate of the average distance between points for a delimited area

I am working with PostGreSQL I have 2 data sets: 1) A points layer where each point represents a building. 2) A Polygons layer created from the points layer using convexhull and clusterwithin ...
0
votes
1answer
33 views

How to calculate nearest point to a linestring in PostGIS?

I'm following this question to calculate POI nearest a road(linestring). I'm able to calculate nearest points in the linestring but I'm not able to find the distance from the POI to nearest point(...
1
vote
1answer
85 views

Creating polygons (squares) from point using PostGIS?

I want to create a regular grid of polygons/squares from this point knowing that between two successive points 100 meters, so each square would have 100*100 m2 in its area.
1
vote
1answer
104 views

Installing Aggregate Polygons Function in PostgreSQL 9.3

I'm new to PostgreSQL and I've been trying to run a sql function written by Dr. Horst Duester, found in http://www.kappasys.ch/cms/index.php?id=65&L=5, which is used to aggregate detached polygons....
3
votes
2answers
85 views

ERROR: Only lon/lat coordinate systems are supported in geography

I'm trying to find out all the circles stored in the database(The circles are present all over the world) that intersect or overlap a given circle. I've stored the circles like this: CREATE TABLE ...
1
vote
0answers
42 views

How to visualize PostGIS data(geography) in QGIS?

I've created a table that stores spherical circles located all over the globe. But I'm not able to view this data in QGIS. This is my table: CREATE TABLE circles(gid serial PRIMARY KEY, the_geog ...
6
votes
1answer
241 views

Replacing corporate GIS? [closed]

I'm looking at replacing our corporate GIS, this is the first time that I have had the responsibility of such a task despite my years of experience with the software as an end user. We will be ...
1
vote
1answer
65 views

How to use the Tile Server to fetch latitude/longitude data

I have installed a tile server using the following link and my main purpose was to geocode as many times as possible. Initially I was using openstreet API to geocode but they have certain limits so ...
2
votes
1answer
172 views

How to calculate width and lengths of street layer in Postgis/PostgreSQL?

I was able to project the point location (ad.shp) to the nearest street (line.shp) like this. My approach was like this. I used ST_ClosestPoint() to iterate this for every point location within a ...
0
votes
0answers
24 views

line_locate_point: 1st arg isnt a line

i used this query for routing procedure SELECT pgr_nodeNetwork('edges', .006); I am following this workshop http://workshops.boundlessgeo.com/tutorial-routing/ and this is the error, I can't ...
0
votes
0answers
39 views

Creating a table and inserting selected streets using plpgsql functions

I am trying to select all streets within 50 meters distance from each address location and store them in a separate table using Postgresql (plpgsql) functions. So far, I have written the following two ...
2
votes
2answers
46 views

PostGIS: ST_DWithin: Why is the output empy?

I have just one table of street segments of Berlin (OSM Data, imported using osm2po, SRID set to 25833). I want my query to output all street segments, that are within a certain distance (e.g. 2000m) ...
1
vote
2answers
42 views

Select transposed values

I am trying to do a conflation. This is my method, and my problem: METHOD Step 1: I have two tables with road geometries. I am using ST_Dwithin to subselect geometries from table 'A' that lie within ...
2
votes
1answer
37 views

Is there a way to automatically displace colliding labels?

I have a PostgreSQL table, which i'm loading on QGIS Essen, that relates many rows to the same object and all this rows have distinct values in the field i'm labelling the objects with. Hence, when I ...
5
votes
2answers
10k 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 | ...
1
vote
0answers
27 views

How can I style a feature that has multiple values for the same field?

I'm opening a PostgreSQL Table on QGIS which has multiple rows related to the same object (think of how you'd register distinct apartments on the same building). I do get to style it based on any of ...
1
vote
1answer
34 views

Find row with a polygon containing a specific lat/long

I have a Postgres table that has two columns – address, and geom (which is a multipolygon without any SRID set, but it should be 2263). I'm trying to find all of the rows that contain a given point, ...
2
votes
0answers
36 views

How to count near intersecting polylines and merge them?

I have a dataset with several polylines created by GPS points of car trips. I am trying to create a simple heatmap where roads with more traffic (where polylines intersect) get darker. I already ...
0
votes
0answers
9 views

how to fetch fields having difference in values from two tables in postgresql

I have two tables with same fields.I want to return fields whose values doesn't match with other table field values. tableA field1 | field2 | field3 | 1 | 2 | 3 | ...
0
votes
1answer
48 views

Using pgRouting through PostgreSQL

I followed the guideline from this tutorial to use pgrouting through PostgreSQL but I can't understand the first instruction: CREATE OR REPLACE VIEW road_ext AS SELECT *, st_startpoint(the_geom), ...
1
vote
0answers
37 views

Cannot store raster in custom database schema

I want to store an elevation raster in a postgis database. However a problem occurs. I use the following command: raster2pgsql -I -C -e -Y -F -s 4326 -t auto dem.tif schemaname.filename | psql -U ...
0
votes
2answers
113 views

How to load all states of TIGER into PostgreSQL?

I downloaded TIGER2014 from the Census FTP and I was wondering how I would be able to load all the geometries at once. I learned about loader_generate_script and loader_generate_nation_script but I ...
1
vote
1answer
73 views

Select points based on value and proximity

I have a set of points in a table, each has a value between 1 and 100. How could I reduce this to the point with the highest value within a radius distance X. So that no point is ever closer than X ...
3
votes
1answer
40 views

QgsDataSourceuRI and left outer join

I'm trying to load data from different tables using left outer joins into a QgsVectorLayer. The approach I'm following so far is the one from the developers cookbook here. The underlying database is ...