PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

0
votes
0answers
3 views

GeoJSON, Google Maps, and 'lazy loading'

One of my projects involves a 'live-fetch' of geoJSON-ified data from a GIS-capable PostgreSQL server: I'm examining its performance as an alternative to tiling. Our tiling goes all the way to zoom ...
1
vote
1answer
13 views

Changing projection of PostGIS data? [duplicate]

I have exported osm data using osm2pgrouting in the database, it has SRID 4326. I want to change projection of all the tables created to a particular SRID in UTM. I read the post FIrst and Second, ...
0
votes
1answer
15 views

Cannot load postgis query as layer in QGIS using create table command

I've a multilinestring object and a points object , where the points are located on the line. The user can set a start and end point on the line and my goal is to return a subset of the points that ...
0
votes
1answer
15 views

Make certain countries visible on Carto map

I want to show only APAC countries on the carto map. I re-wrote my SQL query (see below) and clicked 'create a dataset from query'. Although the countries specified were coded in colors, those outside ...
0
votes
1answer
27 views

carto.com when 2 points have the same location

Carto.com: We are mapping homicides and when two deaths occur at the same location, how do I prevent 2 points with the same lat, lon from overwriting each other? I want to be able to view each point ...
0
votes
0answers
23 views

Strange behavior of ST_length and ST_difference in POSTGIS

I've a LINESTRING that represents the route between Los Angeles and Austin. I calculated the total length of this linestring using ST_Length. Then I calculated the difference between a MULTIPOLYGON ...
1
vote
1answer
40 views

Restoring PostGIS 2.1. dump in PostGIS 2.2

I tryed to restore my old PostGIS dumps (Postgis 2.1, PostgreSQL 9.1) at my new system (PostGIS 2.2 and PostgreSQL 9.5) I was using pgadmin3 to dump the databases and was now trying to restore it ...
1
vote
1answer
20 views

Setting names for attributes in properties in json returned by postgresql

I am trying to make a webmapping app that uses on the server side node.js and postgresql(PostGIS). what I am doing is retrieving data from the Postgresql database using node.js and sending the result ...
1
vote
2answers
34 views

Unable to insert data into table using geocode()

I am new to the world of PostgreSQL. I am using PostgreSQL 9.5. I have installed PostGIS 2.2.2 plugin and along with that came Tiger, Tiger_data schemas. But the Tiger_data schema does not have any ...
0
votes
0answers
21 views

Creating 3D Geo-Database System with QGIS

Currently I am working on a project to create a Virtual 3D City Model. I have Digital Aerial Photos, Digital Terrain Models, Geo-referenced Thematic Data, 3D Geodata, Digital Architecture Models, and ...
0
votes
0answers
12 views

Error occurred in e:\sdk\vc9\gdal-2.1\gdal\ogr\ogrsf_frmts\openfilegdb\filegdbtable.cpp at line 717

I was trying to copy feature classes from file gdb to postgresql using ogr2ogr. Although some feature classes are copied successfully, I encountered some errors while copying other feature classes. I ...
0
votes
1answer
21 views

How to count point attributes that fall within a polygon in postgis?

I have a point shapefile called pointA that has a field called fieldB, and a polygon file called polygonC, and I want to calculate the sum of fieldB for all the points that are within polygonC How to ...
0
votes
0answers
16 views

Upload raster to postgreSQL

I have a raster and I need to upload it via web interface into PostgreSQL table when I need to upload my file CREATE TABLE public.eau_param_hydro ( rid integer NOT NULL DEFAULT ...
0
votes
0answers
23 views

QGIS crashing when trying to open PostGIS layer

I'm trying to open a fairly large dataset from a postgres database in QGIS. The table contains over 100 million rows, and it about 60gb in size. When I try to open this table in QGIS (have tried 2.8 ...
0
votes
1answer
13 views

Problems with conversion of multilinestring to linestring

I'm trying convert a column of datatype multilinestring to linestring using it sql ALTER TABLE streets_nordeste ALTER COLUMN geom TYPE geometry(linestring,3857) USING ST_GeometryN(geom, 1); But the ...
0
votes
1answer
35 views

In cartodb, how to automatically update the_geom with new longitude and latitude?

I am currently posting new table row using the cartodb sql api with the following commands: update {table_name} set geom = ST_Transform(ST_SetSRID(ST_MakePoint(latitude, longitude),4326) latitude and ...
1
vote
2answers
35 views

Creating a table that stores Distances and other column

I am looking to run a query to calculate the distance from a polygon to other features (will use this query on point, polygon, and line features) within 2 miles and create a table that will display ...
1
vote
3answers
32 views

Calculating the area of polygons within bigger polygons

I work with PostGreSQL and have 2 tables, both in EPSG 2950: Table 1 has a couple of big polygons and table 2 has smaller polygons that INTERSECTS with the polygons of table 1. I am looking for a ...
1
vote
1answer
32 views

shp2pgsql-gui not work after Ubuntu update

I'm using Ubuntu 14.04 with postgres-9.5 and postgis 2.2.2. Previously I performed shp2pgsql-gui to access the interface, but now, the terminal shows me this message: the program shp2pgsql-gui not ...
2
votes
1answer
46 views

CityGML: Calculate orientation and inclination angles from a polygon's LinearRing coordinates

I have multiple CityGML files of different cities. Only one of them is in such a good condition that it includes the orientation and inclination angles of every roof polygon (as a gen:stringAttribute)....
0
votes
0answers
20 views

Node Mapnik PostgreSQL vector tiles Question

I am pretty new to Mapnik and Node. I don't understand much so far thats why I have some questions and it would be nice if someone who knows the topic has time to answer. I set up a an OSM database ...
1
vote
0answers
23 views

Using Postgresql/PostGIS/Pgrouting in PYQGIS Processing Scripts

I have started exploring PYQGIS scripts and learning writing PYQGIS user scripts in python. I want to use pgsql/postgis/postgresql in the script. Please suggest Also Can anyone suggest That the ...
0
votes
0answers
34 views

Is ST_Geom included in AWS RDS postgres 9.5?

I have a database instance on AWS RDS using postgreSQL 9.5. I am using ArcGIS desktop 10.4 and connecting to this database. The Arc docs mention needing to place the ST_Geometry.dll in the postgres ...
1
vote
1answer
58 views

From a lat/lng point, load data clusters of other lat/lng points within radius using PostGIS

Very similar to how Zillow's mobile app mapping search works, I'm trying to pass in the lat/lng points & radius (meters) of a single user and then, on the fly, generate clusters to enable passing ...
0
votes
1answer
33 views

pgrouting to calculate distance using a chosen highway system

I am working on a research to calculate the distance of firms (scattering around the nation) to several metropolises via selected national highway system. Then I overlay the highway map with firms' ...
2
votes
1answer
54 views

Shapely & RTree vs. PostGIS/PostgreSQL?

I would really like some insight to the pros and cons of one approach versus the other. I recently wrote some python code that inspects two layers of the same test ESRI GDB file using RTree (bounding ...
1
vote
1answer
22 views

How to load specific PostGIS features from a given table into QGIS?

I have a table in my database built like this: contour_id / (INT) project_id / (INT) contour_lvl / (INT) contour_geom (MultiLineString Geom) Each row in this table is a contour feature associated ...
2
votes
0answers
28 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 ...
1
vote
1answer
27 views

ST_Contains. Check for true/false in Python cursor

I would like to create a list of toponyms, which are within some distance from a point. I have calculated a buffer (dbrows_transf_back) and now I am checking if toponyms from a gazetteer (dbrows_os) ...
0
votes
0answers
41 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 ...
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 ...
0
votes
0answers
41 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 ...
0
votes
0answers
39 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 ...
0
votes
1answer
27 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 ...
2
votes
1answer
50 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: ...
1
vote
0answers
37 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
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
36 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
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
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
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(...
3
votes
2answers
82 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 ...
0
votes
1answer
78 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
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
38 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) ...