17 votes
Accepted

Prevent export to shapefile from PostGIS table in QGIS

Once you allow a user to select the data from your database there is no way to prevent them doing what they like with the data. If it is important to you that the user can only view and not edit the ...
Ian Turton's user avatar
  • 81.5k
13 votes

Poor performance with storing large rasters in PostGIS and visualising in QGIS

If you want to display large rasters in QGIS, you have to build pyramids, either for a tif image on the file system or for a image registered in Postgis. The performance difference in QGIS rendering ...
jgrocha's user avatar
  • 5,345
11 votes

Reading/writing local datastore for QGIS?

How about a local PostGIS instance? PostgeSQL can be downloaded from https://www.postgresql.org/download/ to which a PostGIS extension can be created to make it a spatial database. Also recommended ...
Martin Hügi's user avatar
  • 3,612
10 votes

How to open QGIS project without being asked for PostGIS credentials every time

A very good solution is to use a connection service file. Using this, you can have your credentials in one file, completely independent of QGIS. You can share the project. Other users can use the same ...
jgrocha's user avatar
  • 5,345
9 votes

Adding PostgreSQL database to web map

You can't talk directly from a web browser to a database, because a browser runs code on a client's computer, and that doesn't (and shouldn't) connect directly to your database. What you need is a ...
Alex Leith's user avatar
  • 13.5k
8 votes

Prevent export to shapefile from PostGIS table in QGIS

Some time ago I had the same challenge. What I did was remove the button export to the interface qgis to users who didn't have a permission. How to? I leave some tips... QGis interface uses the ...
d345k0's user avatar
  • 176
7 votes

Clustering of Spatial Data in R or Python

There are an extremely large number of approaches to clustering, and your question is not answerable, short of writing a textbook describing all possible methods. Therefore, the question I will answer ...
Lee Hachadoorian's user avatar
7 votes
Accepted

Adding Timestamp to attribute table in QGIS

Using the QGIS field calculator you can create an expression on a Time type field, with the speed as a variable. Changing the speed and re-calculating the expression will adjust the times accordingly. ...
Matt's user avatar
  • 16.9k
6 votes

Poor performance with storing large rasters in PostGIS and visualising in QGIS

Not sure if it was your case, but I found out -I should not be used together with appending data -a. I was importing many TIF files into a DB, and -I actually creates the index again and performs ...
Raphael Jolivet's user avatar
5 votes
Accepted

Normalizing a spatial datasource (5NF)?

You probably won't use your data in normal form, your tools will expect relatively flat, denormalized inputs. You probably won't update your data in normal form, you'll receive flat, denormalized bulk ...
Paul Ramsey's user avatar
  • 19.9k
5 votes

Storing different shapefiles as "one" in QGIS?

By definition, a shapefile can store one specific type of geometry. So it would not be possible to store layers of different geometry in one shapefile. One way to share the data would be to share the ...
nash's user avatar
  • 1,976
5 votes
Accepted

Appending characters before the filename of all layers in a GeoPackage in QGIS?

You must rename layers one by with SQL ALTER TABLE... See https://gdal.org/drivers/vector/gpkg.html. For renaming many layers automatically you should make a script that first gets the list of the ...
user30184's user avatar
  • 65.5k
4 votes
Accepted

How to split ESRI file geodatabase into two or three by region?

I'm assuming you are trying to do this split spatially so one employee can work on a half while the other employee works on the other half. I would use model builder. Define the two areas you want to ...
Adam Kara's user avatar
  • 916
4 votes
Accepted

subtract polygons from other polygons with SQL

If you are working with SQL Server 2012+ then you should be able to do this within a single query. Don't expect it to be very performent, but do make sure that you have all the appropriate indexes (...
MickyT's user avatar
  • 3,430
4 votes

How to transfer table from localhost server to another server in PostgreSQL

In a terminal you can use a command line like : pg_dump -t "schema1"."table1" -h host1_ip -U username1 dbname1 | psql -h host2_ip -U username2 dbname2; This would transfer table1 (in schema1) from ...
dd_a's user avatar
  • 115
4 votes

Why does rendering from a view take significantly longer than rendering from a table?

I believe that the underlying query (used to make the view) will be executed every time you try to access the view, where if you are just using the source table the select will much simpler. This ...
Heath's user avatar
  • 91
4 votes
Accepted

PostGIS - query maximum distance between two polygons

There is no such function, but you can create the functionality yourself (although it can be slow for large datasets) by calculating the distance from every point in the red polygon to the blue ...
tilt's user avatar
  • 3,269
4 votes
Accepted

Difference between SpatialJoin with "...where..." and "join...on..."

No one difference for both statement. Postgresql converts both it to the same query plan. You can see plans by just add EXPLAIN ANALYZE before your select and looks for result.
Roman Tkachuk's user avatar
4 votes
Accepted

Storing different shapefiles as "one" in QGIS?

You can revert back to Spatialite databases. Use the extension "QSpatialite" to create an empty DB (use the creation button, circled on the capture below), and add your layers to it. To add a layer, ...
Jerry Magnin's user avatar
4 votes
Accepted

Visualize geographic spatial data from SQL Server directly in PowerBI

So my research at this point points to NO... which is startling. I even watched an entire ESRI UC presentation on the subject from last summer (link), and even investigating the Mapbox components, ...
DPSSpatial_BoycottingGISSE's user avatar
4 votes

GIS Database format functional differences

It sounds like you have two paths, but need to distinguish each option. Files on a shared drive - SQLite/Spatialite is a file-based database that may serve the purpose. GeoPackage are flat files much ...
DPSSpatial_BoycottingGISSE's user avatar
4 votes

I just imported some shapefiles to a PostGIS database, where can I find the files in my drive?

PostgreSQL uses an internal, efficient storage for the data. There are no human-readable or human-identifiable files in your filesystem. Your imported geo data is now accessible via SQL only.
bugmenot123's user avatar
  • 11.1k
3 votes

How do partition tables improve query performance in PostGIS?

Partitioning is most beneficial when you can couple it with a constraint that will allow the planner to exclude certain child tables entirely from most queries. Unfortunately, this currently isn't ...
dbaston's user avatar
  • 13.1k
3 votes
Accepted

How to update PostGIS column with distance from features in the same table

Essentially what you're wanting to solve is a nearest neighbour problem, and the main part of your query should look like the following: SELECT DISTINCT ON (t1.gid) t1.gid, t2.height FROM mytable AS ...
user2642365's user avatar
3 votes

Can spatial data in Oracle be imported into SQL server?

Creative solution: let Oracle produce the insert Statement for SQL Server. Example: select 'Insert into cc.yourGeometryTable (ID,OBJECTID,GEOMETRY) values (''' || id || ''',' || OBJECTID ...
GeoTask's user avatar
  • 31
3 votes

Calculate centroid of a point cluster

I am not sure I really understand what you are doing. How can a point cluster be a collection of linestrings? , but .. Remember you are using a spatial database, why not use the functionality in ...
Nicklas Avén's user avatar
3 votes

How to transfer table from localhost server to another server in PostgreSQL

There are two ways that are depending on PostgreSQL version and connectity. Use the backup (for all versions, and integrated in most admin tools): With pgdump you can export your table, send the ...
Matte's user avatar
  • 6,235
3 votes

Why does rendering from a view take significantly longer than rendering from a table?

I've noticed the same thing on spatial views that return a large amount of data in PostgreSql and it is caused by QGIS issuing a SELECT count(distinct (id))=count((id)) FROM schema.view to verify the ...
Jay Cummins's user avatar
  • 14.6k
3 votes

Requirement to use a spatial database - Why use a spatial database?

A spatial database doesn't hold shapefiles per se. However, it does store geometry associated with a feature/row. In your case, the boreholes would be stored as points with an x,y,z coordinate. It'...
Fezter's user avatar
  • 21.9k
3 votes

Convex hull with n points using computational geometry libraries

ST_ConvexHull It is often used to determine an affected area based on a set of point observations. Also worth nothing, It is usually used with MULTI and Geometry Collections. Although it is ...
Evan Carroll's user avatar
  • 7,071

Only top scored, non community-wiki answers of a minimum length are eligible