PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

1
vote
1answer
66 views

Syntax error at or near “1” — PostGIS in Action — Chapter 11 SQL data dump

I'm following along with PostGIS in Action, 2nd ed., chapter 11, while attempting the SQL dump from the data files and I'm getting the following error: ERROR: syntax error at or near "1" LINE 189: 1 ...
2
votes
0answers
39 views

How to display join query results on a sublayer in CartoDB.js application?

I'm working with my first CartoDB.js application. I created a visualization on the app using cartodb.createVis('map', visjson_url, opts); The app has two layers, base map and data layer. the data ...
3
votes
1answer
104 views

How to cast geometry to varbinary in PostGIS / PostgreSQL

In MSSQL Server, casting geometry to varbinary(max) produces a string that looks like this: 0x3D0B0000010CA0F3BE3B5CE9474100EC414E3AF93941 When you insert that string into a geometry column, a ...
1
vote
1answer
61 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
133 views

How to use ST_Difference to create linestrings from non-intersecting polygon boundaries?

There are two tables with polygon features in my PostgreSQL database. I want to create linestring features where the boundries of the polygons are not intersecting. CREATE TABLE schema.poly_a ( gid ...
1
vote
0answers
40 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 ...
6
votes
2answers
189 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
0answers
71 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
1answer
60 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 ...
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 ...
4
votes
0answers
33 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 ...
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 ...
1
vote
0answers
27 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
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 ...
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 ...
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
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 ...
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 ...
2
votes
1answer
58 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 ...
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
83 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 ...
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 ...
4
votes
1answer
107 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 ...
2
votes
2answers
83 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, ...
0
votes
0answers
51 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 ...
4
votes
2answers
70 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 ...
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 ...
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 ...
5
votes
2answers
192 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 ...
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 ...
1
vote
0answers
52 views

Multi-dimensional points in PostGIS

I have experience in PostgreSQL but I am fresh new in PostGIS and I can see how quickly would I like it. I have extensively worked on time series and now I am discovering the spatial side of my data. ...
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 ...
1
vote
2answers
306 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 ...
0
votes
1answer
57 views

Where to download .osm files and how to convert it to .xml? [closed]

I am new to deal with OSM data, and I want to download only .osm files not .pbf. I tried geofabrik.com but it provides .osm.pbf file format where can I download .osm files? is there any recommended ...
3
votes
2answers
95 views

What is an undirected graph with cost and reverse cost?

What is the purpose of a graph that is "undirected, with cost and reverse cost"? Where is the difference to a graph that is "undirected, with cost" ? For me it does not really make sense, because for ...
1
vote
1answer
71 views

Bounding rectangle of a geography

How to get a bounding box (in the form of a Polygon, or anything else where it's easy to extract the corner coordinates from) of a given Geography item? Something that would look like: SELECT ...
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 ...
0
votes
1answer
73 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
42 views

osm2pgsql password prblem

I'm totally new in PostgreSQL. I followed this guide to set up Postgre and Postgis: http://learnosm.org/it/osm-data/setting-up-postgresql/ I created an OSM database and the Postgis extension. I ...
1
vote
1answer
41 views

Get lat and long in two columns as floats for all points in MULTILINESTRING in PostGIS?

ST_DumpPoints(geom) gives me something like this: ("{1,1}",0101000020E61000002DEDD45CEEB362C0CE1951DA1BC24E40) ("{1,2}",0101000020E6100000C94AD826F1B362C09D4B7BC72CC24E40)... Where I want it to ...
2
votes
1answer
52 views

Improving performance when importing file geodatabase into PostgreSQL?

I have a file geodatabase created with ArcMap 10.1 that contains one feature class with 650 million points. The feature class contains the shape field and an identifier. The .gdb is approximately ...
0
votes
0answers
36 views

How to query Tiger Data for Layers?

I am new to this. I have data downloaded from ftp://ftp2.census.gov/geo/tiger/TIGER2015/ and I would like to learn more about how I can query that data for the different layers it offers. Here is ...
2
votes
2answers
81 views

How to find the closest segment to a point

I am trying to find the closest road segment to a point that I have and return an attribute from the closest segment to the given points. I know in PostGIS I need to use ST_ClosestPoint, ...
1
vote
1answer
102 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 ...
2
votes
1answer
43 views

coordinates point in any reference system

I have a coordinates point like this "01010000003413056727B71D41C7F1A68B1ADC4141", but I don't know it is what. Can you aswer What is this?
4
votes
1answer
50 views

Overlapping line aggregation+trim in PostGIS, while summing values

I'm sorry for the vague title, I didn't really know how to name this. I am working on a project that visualizes and simulates 17th century trade. For this project we also use so called Minard lines to ...
2
votes
2answers
106 views

How to select 200 random points within a 100m radius in Postgis

I'm currently learning to use PostgreSQL and PostGIS. I would like to know how to write the SQL that would allow me to select 200 random points within a radius of 500m from a user defined centre ...
1
vote
1answer
72 views

postGIS, length, area, density of line network

I am trying to figure out the length and density of all major roads and minor roads within a study area to return something like: ...