Questions tagged [postgis]

PostGIS is a database extension for PostgreSQL providing additional spatial types, indexes, functions, and aggregates.

Filter by
Sorted by
Tagged with
0
votes
1answer
26 views

Unable to import shapefile into PostGisDatabase - problem of connection

I have been trying to import/load a shapefile into a PostGis database without any success despite many trials and searching an answer in the documentation. Configuration I'm running Windows 64 bits ...
1
vote
1answer
286 views

Howto do a hard upgrade to postgis-1.5 to postgis-2.0

I need to upgrade from postgis 1.5 to postgis 2.0 but i cant backup my data and restore it can someone give me a link where it is clear enough for doing this?
0
votes
2answers
817 views

Psycopg2 Errors on SQL statement when trying to copy data from CSV file into PostgreSQL database

I am not a developer or PostgreSQL DB admin, so this may be basic questions. Logistics: Windows 10 server / pgAdmin 4 / Postgres 10 / Python 2.7.13 I'm using a python script to ingest external data, ...
1
vote
1answer
404 views

KNN search two arrays with 2000 elements

What I did: installed postgresql 9.3.1 installed postgis 2.1.2 I tried to find the distance by selecting column input_variable_1 with value 50. I got the following result. select input_variable_1 &...
2
votes
2answers
371 views

How to dump a postgreSQL/postgis sample database filtered by a spatial extent?

My goal is to extract a subset of my postgres/gis database to create a sample dump which has to have the same data model with relations between tables and views, but which has a smaller spatial extent....
2
votes
1answer
24 views

Is there a reason I should not set my default data location to google drive when setting up PostgreSQL?

I'm brand new to working with databases and am setting up a PostgreSQL database which will be used for personal projects. I'm setting it up with the postgis extension and will use both for some basic ...
0
votes
0answers
39 views

Installation of PostGIS extension not working on a docker image (seems not found by further commands)

Context I have a PostgreSQL code for installing the PostGIS extension which is working great on my machine but which doesn't work on a docker image. I cannot understand why. Here are the ...
0
votes
1answer
455 views

postgresql statement taking ages to cancel in pgAdmin III

I was running a query in pgAdmin III and it was taking a long time. After 9 minutes I decided to cancel it using the stop button. The UI stated that it was cancelling and stayed in that state for ...
0
votes
0answers
8 views

Losing connection to the server when using PG Routing function pgr_alphaShapes

I am running PG Routing v. 3.0 on Postgresql 12.2 and the pgr_alphaShapes function is giving me a lot of trouble. Following the documentation here, I passed a geometry and a spoon radius. SELECT * ...
0
votes
2answers
22 views

How to get the type of geometry column in postgres?

I need to determine the type of geometry column and doing it with the following query: SELECT type FROM geometry_columns WHERE f_table_schema = 'public' AND f_table_name = 'table_name' ...
1
vote
1answer
25 views

Create 7 day rolling average in PostGIS for each feature

I have a timeseries dataset that looks like this: uid | geom | date | count | ------------------------------------- 1 | FeatureA | 2016-02-01 | 1 | 2 | FeatureA | 2016-02-02 | 2 ...
0
votes
0answers
15 views

Can't get PostGIS to show points on map - no button to activate map?

PostgreSQL 12 and PostGIS 3.0.1 on Windows 10, using PGAdmin4. I'm totally new to PGAdmin4 having recently come from PostgreSQL 9 / PGAdmin III - frankly, I don't like it but I'm trying. For the ...
0
votes
0answers
7 views

Library that can generate random geospatial data in GeoJSON

Is there a library that can help to quickly generate random geospatial features in GeoJSON format (preferably, but can be anything), based on the parameters that we pass in. For example - generate ...
2
votes
1answer
45 views

Best indexing strategy for spatial join and ranged lookup

Context We have two tables (not the real tables used in the examples below. They are from a toy DB im using for testing): Incidents_2 (Columns of interest are a geom, and a reported_at (int8)) ...
0
votes
0answers
16 views

PostGIS buffer in miles not lat/long degrees

I am trying to take two points, add a buffer around them (in meters), draw a line between them, draw a buffer around that, and then query whether items are inside the area of the circles expanded line ...
0
votes
1answer
51 views

PostGIS trigger is fired without error, but the action seems not executed

Context I have set up a trigger on a PostgreSQL (+Postgis) table containing observation points. This trigger must compute a new geometry for each inserted or updated point. This new geometry must be ...
0
votes
1answer
24 views

Aggregating data by group and time gaps

I've got a postgresql+postgis database filled with GPS data: CREATE TABLE "my_table" ( "id" serial NOT NULL PRIMARY KEY, "gps_id" text NOT NULL, "lat" numeric NOT NULL, "lon" ...
0
votes
1answer
199 views

'ERROR: could not access file “$libdir/postgis-2.5”: No such file or directory' [closed]

Dealing with PostGIS has been a nightmare from day one. Every single time I have to touch it in any way, or update PostgreSQL, it turns into a broken mess. The only reason I use it is because I need ...
0
votes
1answer
29 views

How do I check if PostGIS is installed in my PostgreSQL?

I'm trying to check whether or not the PostGIS extension is installed, as a PG query. I'm unable to find this info out.
5
votes
1answer
1k views

Optimizing an Intersect query between two huge spatial tables

I am having a hard time trying to improve an intersect between two spatial tables and I would like to receive any tips about the table designs, queries or dba configs. Tables: Table teste....
1
vote
0answers
73 views

Installing Postgis and PGRouting via pgenv

We have a product where our production environment uses Docker and has no issue with installing things like postgis and pgrouting. However, we have multiple remote developers and many of them (...
1
vote
1answer
124 views

PostgreSQL - PostGIS ST_SetValues - int array cast to double and back

I am using ST_SetValues with array defined as ARRAY[[9, 9], [9, 9]]::double precision[][]. If I store 32bit integer value in this array can I retrieve this value exactly after casting from double to ...
1
vote
1answer
17 views

Would it be worthwhile to use the PostGIS extension for custom planets?

I need to be able to store latitude and longitude in several different tables. My understanding is that PostGIS has a geography column type that does this. However, I'm not storing coordinates on ...
15
votes
3answers
11k views

PostGIS vs. SQL Server for GIS data

So I'm recently starting at a new company and have a lot of ArcGIS users who seem really keen on going forwards with a PostGIS instance to serve some data to our customers. While I don't have an issue ...
0
votes
1answer
100 views

Postgis distance filter does not work as expected

I have a issue with simple PostGIS query. Here is a query: SELECT id, location, ST_Distance_Sphere(location, ST_MakePoint(21.012228699999998, 52.2296756)) AS distance FROM pins WHERE ...
0
votes
1answer
113 views

Copy postgis layer from S3 to Heroku

I have a dump of a postgis layer (layer.dump), which I am trying to add to my heroku database (mydatabase). The postgis layer is stored on S3 (https://s3.amazonaws.com/layer.dump). I would like to add ...
0
votes
1answer
30 views

Query table based on column value

I've those tables on my PostGIS db. poi_list +----+------+-----------------+---------------+--------------------+ | id | nr | source_layer | source_column | source_id | +----+------+----...
0
votes
1answer
254 views

How to make ST_DWithin to use meters for distance in Postgis?

I have already asked a similar question here: Find the nearest geo points across two tables with Postgis (or without)? but now I am stuck around the correct geo-reference to calculate the distance ...
0
votes
1answer
3k views

could not load library “/usr/pgsql-10/lib/postgis-2.5.so”: /usr/pgsql-10/lib/postgis-2.5.so: undefined symbol: GEOSFrechetDistanceDensify

I have installed postgresql 10 on centos 7.4 and installed postgis2.5 few months back. The installation was successful and Iam able to use postgis. All of a sudden Iam getting the following error. ...
2
votes
1answer
689 views

Unable to send WHERE parameters to subquery

I have the following problem (in PostgreSQL in combination with PostGIS): I have the following nice query who clusters multiple records. Although it uses PostGIS function, my problem is not PostGIS ...
0
votes
0answers
31 views

'pg_query_params(): Query failed: ERROR: relation “postgis.spatial_ref_sys” does not exist'?

QUERY: SELECT proj4text FROM PostGIS.spatial_ref_sys WHERE srid = 4326 LIMIT 1 I have no such query anywhere in my code. 100% of all my references to PostGIS are done like: SELECT whatever FROM "...
0
votes
1answer
18 views

Why does PostGIS create an ugly table in my database called “spatial_ref_sys”?

I need to use PostGIS, and do use it, but it bothers me that it created this ugly "spatial_ref_sys" table in my database, in the main (public) schema, where I keep all my own tables. It looks ugly. ...
0
votes
1answer
61 views

Are there limitations on using pg_dump on a materialized view such that the data won't be included in the results?

I have a database, call it maps, which contains 2 schemas, a & b. In each schema I have a number of tables: a.t1, a.t2, b.t1, b.t2 (plus others). The column sets on each of these tables is ...
4
votes
1answer
2k views

Does PostgreSQL/PostGIS use R-Tree or R*-Tree?

PostgreSQL and PostGIS offer to create indexes over GiST with pretty much the same code. On 2D items, "This gives R-tree behavior" is what it says in the PostgreSQL source comments. However, it is a ...
0
votes
1answer
21 views

group several columns together in materialized view

For statistical reasons, I need to group several ids in a single column of a materialized view. This should form a list of ids from other tables with an info. Further processing will be done on this ...
0
votes
1answer
14 views

Restrict access to my PostgresSQL/ Post GIS database used through QGIS

We have a small network of users accessing our QGIS data stored on a PostGIS database (in 3 schemas) which sits on my local drive in my computer. This drive is then shared with the others. Due to the ...
4
votes
1answer
2k views

PostgreSQL 9.3: pg_dump fails due to tiger.direction_lookup

I am trying to dump the schema from one of my PostgreSQL 9.3+PostGIS databases: /usr/lib/postgresql/9.3/bin/pg_dump --schema-only -h stg-db.....rds.amazonaws.com ...
0
votes
2answers
46 views

How to use pg_restore in database with different postgis installation (postgis schema location)?

I have database1 where postgis is installed in the public schema. And database2 where postgis is located in a schema called postgis. When I dump database1.schema1, schema1.table1 references its geom ...
-1
votes
1answer
217 views

PostgreSQL 9.5 and PostGIS on OSX

I have installed PostgreSQL 9.5 on OSX using the EnterpriseDb package. How do I install PostGIS on it? The official PostGIS OSX Page says that: The OSX PostgreSQL/Stackbuilder combination from ...
0
votes
3answers
18 views

query two point values from a table then insert a new record after creating a line from the two points

I am running postgres: 9.5.19 I have already point values as separate records in a table. I want to create a new record which represents a line geom derived from the two points above. I have lots of ...
0
votes
0answers
20 views

PostGIS Issue with not displaying out of DB rasters correctly in QGIS

I am trying to create an out of DB raster from a VRT in postGIS and displaying it in QGIS using DB manager. I am creating the VRT using absolute paths and have checked using PGAdmin that the DB is ...
0
votes
1answer
30 views

Postgres/PostGIS is returning data from a table that doesn't exist (I'm a newbie — show me why I am wrong)

I am following a tutorial for PostGIS. I have uploaded all the example datasets (all shapefiles) into a PostgreSQL database, although I have modified the names so they do not all match the names of ...
0
votes
1answer
49 views

Multiple versions of PostGIS and PgRouting on CentOS 7

I'm trying to install multiple versions of PostGis and PgRouting in the same instance of PostgreSQL v9.6, using CentOS 7.5. I've been fighting with dependencies and shared libraries, to make all ...
0
votes
1answer
15 views

Store lat lon in addition to the geography data type?

It's my first time using Postgis, I have been doing some reading on using it. I have added a geography data type to my table, but the values are stored in a binary format. Is it good practice to also ...
1
vote
1answer
209 views

How can I create a copy of postgis db from one windows machine to another

I have postgres 9.3 database with postgis extension. I want to create a new database on different computer and import all the tables from the source database. In mssql it would be simple , just ...
2
votes
1answer
29 views

PostGIS Intersects Query takes lot of time to get the result

I am developing a webmapping application in which I used PosgreSQL with PostGIS plugin as my DB. I have this two tables namely; evidensapp_polystructures and evidensapp_seniangcbr. Both tables ...
0
votes
1answer
37 views

How can I find the longest record (in characters) for each column in a table in PostgreSQL

I couldn't find the answer to this on here so sorry if repeated. I'm looking to query a table to find the longest record in characters for each column in a specific table. I have a table which is ...
0
votes
0answers
33 views

Query to reduce column length

I'm new to PostgreSQL so sorry if I haven't explained myself clearly! I've used the below query to find out the max character length of each column in my table. I now want to be able to shorten each ...
0
votes
1answer
46 views

Indexes for combined time and location data

I have a multi-TB table with geolocation data for a certain item id and timestamp. It is partitioned by time with a partition for every month (~500GB), and clustered (including the partitions) on the ...
15
votes
3answers
10k views

PostgreSQL vs. MySQL: spatial feature comparison

We are the in the process of building out a web application that has a spatial data component. In the beginning our spatial data comparisons will take a given point and return matched overlapping ...

1
2 3 4 5