PostgreSQL is an open source object-relational database system.
1
vote
2answers
49 views
Dismiss invalid Polygons in PostGIS
I Have a massive Dataset from OpenStreetMap and imported it into a Postgres-Database using ogr2ogr. I got several warnings during this process because of multipolygons with too little vertices. Now I ...
1
vote
0answers
22 views
Add polygons to PostGres to be queried by Nominatim
I'm trying to build out a geocoder that also returns the correct legislative district information from this data:
http://www.census.gov/geo/maps-data/data/tiger.html
I've got OSM data imported into a ...
0
votes
1answer
49 views
How can I find the segment of a line that lies within a polygon using Postgis?
Given a "road", represented by a line, that crosses a "state", represented by a polygon:
What is the SQL query I can use to find the segment of the road that is within the state?
A concrete example.
...
0
votes
0answers
38 views
PostGIS Function both work fine
I create two function both work fine same solve, but one of them, take to much time (which one use "temporaly"). Is very important because I will run in several times the funcion.
Please any one can ...
2
votes
0answers
33 views
Need help deciding on a database and datatype [on hold]
I realize that these questions should probably be directed to a DBA but I feel like you might have some experience in managing the same kind of data.
We have an existing system where we are storing ...
0
votes
0answers
19 views
Using postgis functions in ArcGIS Server 10.2 for Layer Definition Query
I asked the previous question
Using PostGIS functions in ArcGIS Desktop for Layer Definition Query?
And got an excellent answer. However this covered ArcGIS Desktop. In 10.0 it seemed that if I got ...
0
votes
0answers
21 views
Determine the list of circles (from 100K circles), that overlap with a given polygon
This question was asked on StackOverflow, came here because no response has been posted so far.
Existing data structure
we have polygons represented as an ordered list of lat/long coordinates and ...
0
votes
1answer
20 views
query to get predicted location after traveling on pgrouting route for some hours, postgis 2, postgresql 9.3
I am using Postgis 2.0 on Postgresql 9.3 on Ubuntu 14.04 x64. I am trying to find an efficient way to fulfill this routing request. So I am able to get the shortest route using the source and target ...
0
votes
0answers
44 views
Setting up PostGIS: Postgres can't see the PostGIS extensions?
I've just installed Postgres 9.3.4 and PostGIS 2.1 on OSX Mavericks using the very latest versions of the KyngChaos libraries. This is a new machine, and I deliberately haven't installed Postgres ...
3
votes
1answer
38 views
Rounding bug using ST_GeomFromGeojson()
Let's say I have the following geographical point represented in GeoJSON:
{"type":"Point","coordinates":[-109.0,35.292506]}
When I run the following conversion, things work as expected:
SELECT ...
1
vote
1answer
36 views
storing date range in PostgreSQL/PostGIS for time parameter in WMS/WFS
Using GeoServer I want to deliver some objects/features with WFS and WMS. The objects and their attributes have a date range during which they are valid. Say, we have street lamps and each street lamp ...
1
vote
0answers
16 views
Import raster to postgis and add a foreign key constraint
I use raster2pgsql to import raster files into a table with this command line:
C:\Program Files\PostgreSQL\9.3\bin>raster2pgsql -s 4326 -M -F -a c:\temp\mun.tif public.basqui_raster | psql -U ...
2
votes
1answer
40 views
How to render overlapping bus trips as one route from OSM?
I would like to create bus map for my city from OSM data.
I imported data into Postgresql with osm2psql and I can render routes with Mapnik. But the problem is that same route ref is shown multiple ...
0
votes
1answer
14 views
PGrouting encapsulated queries error [quotes problem]
I'm facing a quote problem on a pgrouting query.
What I'm thinking is wrong is that my query only works if I use one, then four, then eight quotes on encapsulated queries.
Is there a way to simplify ...
1
vote
2answers
28 views
adding geometries to a source, target table for multiple rows in pgrouting
I am having an issue trying to perform the pgr_dijkstra algorithm and getting back a line in my table for multiple rows. I have a table that has a columns of source, target node id's but I cannot ...
1
vote
1answer
23 views
PostGis line geometry type
I am using postgresql9.3 and postgis2.1.I imported a shape file into my database. In that the_geom datatype is "LINESTRING" . Now i need a query to find the lines within a given radius for a ...
1
vote
1answer
58 views
Is it possible to get a binary geometry from PostGIS?
PostgreSQL (via C# Npgsql driver) returns geometry column (PostGIS) like a hex string (EWKB as I understand) for this query:
SELECT ST_Transform(geometry, 4326) FROM myGeometries;
Result (for ...
1
vote
0answers
62 views
Why does QGIS crash when my PostGIS table has a primary key?
Configuration:
PostGIS Library: 2.0.3 GEOS: 3.3.8-CAPI-1.7.8 Proj: Rel. 4.8.0, 6 March 2012
PostgreSQL 9.2.2, compiled by VC++ build 1600, 64 bit, running on Win2012 server 64 bit
QGIS 2.2.0 - 32 ...
1
vote
1answer
13 views
Access SQL database in postgres for routing
i am trying to access sql database in pgadminIII. Using postgres_fdw I am trying to connect. But I am struck in the middle..Following are the steps I followed
CREATE EXTENSION postgres_fdw;
CREATE ...
0
votes
1answer
31 views
Error importing shapefile into postgreSQL using shp2psql
I've tried this a few different ways and keep getting the same error.
I have a shapefile currently in WGS84 UTM Zone 15 which is SRID 32615.
I used the ArcMap multipart to singlepart tool to create a ...
0
votes
0answers
39 views
Batch join non-spatial csv to shape files
I have 173 point shape files from soil testing and 173 non spatial csv files with lab ressults (one csv for each shape file) that need joining. The files are located in a library with folders named ...
0
votes
0answers
33 views
How to calculate the track length of GPX files with PostGIS?
I am trying to calculate the length of a GPX track. The original GPS data looks like this:
<?xml version='1.0' encoding='UTF-8' standalone='yes' ?>
<gpx ...
2
votes
1answer
57 views
Using PostGIS functions in ArcGIS Desktop for Layer Definition Query?
I am connecting ArcGIS Desktop 10.2 to Postgis 2.0.3 with PostgreSQL 9.2.4. No SDE. I can load data with no problems and access the data in ArcMap. However I can't seem to use PostGIS functions within ...
-1
votes
1answer
40 views
how to build and install pgrouting-2.0 on postgresql-9.3 on ubutntu 12.04 Lts precise?
I try to build following instructions from this link https://github.com/pgRouting/pgrouting after downloading from the github. I have done the cmake command and there is my error output:
...
1
vote
1answer
30 views
Install pgRouting on Postgres 9.1 and Win 32
I referred some documents on GIS-stackexchange and over internet regarding installation of pgRouting on Postgres v9.1 and windows 32.
I am not getting the correct version of pgRouting to install.
Can ...
9
votes
0answers
135 views
How to query OSM for all beaches facing west? - QGIS preferred
I want to identify all beaches on this planet (or a given subset of it) that are facing westwards (+/- 20-30 degrees).
My idea is to use OSM as the data source. Extracting all polygons that have the ...
0
votes
2answers
51 views
Update database via osmosis and osm2pgsql too slow
I got a box with the current hardware config:
CPU: Intel Xeon E3-1220 V2 3.1 GHz
RAM: 10gb
Hard Disk: Western Digital WD10EZRX SATA Hard Drive
OS: Centos 6.5 64 bit
DB: PostgreSQL 9.3.2
and ...
2
votes
1answer
84 views
PostgreSQL : how do I make this sum [closed]
I have this huge table with all the addresses of my country in it. Every house is an object, and there is a column for the streetname (or street ID) and a column for the housenumber.
I want to make a ...
0
votes
2answers
57 views
QGIS Connection PostGIS and shapefile
I'm working on QGIS with a database created in PostGIS and a shapefile. Inside the database, I have geometry type data about location of some points, instead on the shapefile there some selected ...
1
vote
1answer
30 views
Get list of all tables in CartoDB account with SQL statement?
How can I get a list of all available tables in my own authenticated CartoDB account with a SQL statement?
This returns null:
SELECT table_name as t FROM information_schema.tables WHERE table_schema ...
2
votes
2answers
37 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 | ...
4
votes
0answers
33 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 ...
1
vote
2answers
70 views
Searching planet_osm_point by longitude and latitude
I used osm2pgsql to import data into my PostgreSQL database and it put gave me the following 4 tables, planet_osm_point, planet_osm_line, planet_osm_polygon and planet_osm_roads.
I have looked at ...
2
votes
1answer
23 views
Only Draw Geometric Objects Above 8th Parallel North
I have many geometric objects (in both SRID:4326 - the_geom - and SRID:3857 - the_web_geom - in PostGIS extension tables of PostgreSQL), including country boundaries, for the country of Ghana and I ...
1
vote
1answer
30 views
Show raster data in WEB
I have downloaded Landsat8 image and use matlab to processing temperature and vegetation. Then I insert data into PostGIS.
I used QGIS to display the results but i want show in web. How to display the ...
1
vote
0answers
31 views
Focal Mean of neighbors using ST_DWithin on points in PostGIS
I'm trying to calculate focal means on a grid of points in PostGIS.
I know that a raster calculation would be more efficient, but I only need to run the algorithm once to get the data, so I don't ...
1
vote
2answers
61 views
How can I speed up these queries
I am trying to make some calculations using PostGIS and PostgreSQL. There are 3 important tables : wbn, wgo and wvb. These tables have about 500.000 objects in them (geometries). The problem is that ...
1
vote
0answers
29 views
PostGIS 3D Extrude function
I selected buildings from OSM (which represents my city). I insterted a new column 'height' and I filled with a random number exactly number 2, so every building have 2metres altitude. Buildings are ...
0
votes
1answer
9 views
How to get a distinct pair of column values based on minimum of another (computed) column?
I have two tables with point geo data, one (sfc_obs) with weather stations (names), one (smos_grid) with satellite pixels (dggs), and I'm trying to find which dgg is closest to each name using ...
1
vote
1answer
77 views
What index to use in this situation
I am working with 3 tables with 3 x 500.000 rows. There is one colomn that gives each object a unique ID (UIDN), there also is a colomn with the geo data (the_geom). There also are some other columns ...
3
votes
1answer
65 views
MS SQL Server 2012 to Postgresql
How to use MS SQL Server 2012 spatial database in postgresql using foreign data wrapper?
2
votes
0answers
127 views
Raster slightly displaced after exporting from PostGIS database
With the following queries I've exported a raster (GTiff) from my postgis database:
1.Creating a large object:
WITH buffer AS (
SELECT
id,
ST_Buffer(geom,50) AS geom
FROM ...
1
vote
1answer
34 views
3D functions with SFCGAL in PostGIS
I have this setup: "POSTGIS="2.1.2 r12389" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY RASTER" on ...
1
vote
2answers
53 views
What is the best way to take a WKB value in SQL Server Management Studio and create a PostGIS geometry in PGAdmin III?
Normally, I would just use WKT strings but I'm investigating some possible geometry weirdness with the Text methods in SQL Server. I would like to copy/paste the WKB values from the SQL Server ...
0
votes
1answer
94 views
How to save a google map direction into a postgresql/postgis database?
I have taken a direction(route) from google map.
Can I save this resultant direction/path/route to my postgresql/postgis database? How?
2
votes
2answers
43 views
Do I still have to use a template DB for PostGIS?
createdb mydb -T template_postgis
Is this -T template_postgis still necessary in new versions of PostGIS?
I never saw this db being different, it always has those 3 sql files you run there. Is this ...
1
vote
0answers
64 views
Postgresql JDBC Table Valued Parameters
MSSQL has a great feature called Table Valued Parameters. It allows you to pass a table of a custom data to stored procedures and functions.
I was wondering what is the equivalent , if exists, in ...
1
vote
1answer
33 views
convert XML to PostgreSQL using MATLAB
In a weeks time I should be able to transfer the GIS data provided in the XML format to PostgreSQL database. I have prepared the PostgreSQL database (empty) and looking at Matlab scripts. (I am on ...
1
vote
0answers
25 views
qGIS' Database Manager “Load as New Layer” Option Not Working
I'm trying to visualize some of my database queries on qGIS. I run a valid SQL query which returns a subset of my table that should be easily loaded as a layer. I then check "Load as new layer," ...
2
votes
1answer
34 views
Update/refresh edited PostGIS table in QGIS
I imported a PostGIS table (with geometry) from my PostgreSQL database as a layer in QGIS.
Later, I edited the table using PostgreSQL UPDATE commands.
Is there a way to quickly update (like a refresh ...
