Skip to main content

Questions tagged [dbmanager]

QGIS core plugin for managing database connections

Filter by
Sorted by
Tagged with
1 vote
0 answers
20 views

Loading result of PostGIS query that contains temporary table delete statements as new layer

I am trying to run then load the result of a PostGIS query using the DB Manager on QGIS 3.30.2. The query is a call to a PostGIS custom function defined in the database that generates some polygons. ...
Boris CALVET's user avatar
1 vote
0 answers
44 views

Layer creation by running a SQL query in DB manager tool (QGIS)

I just would like to know if there is a way to create a new layer among the virtual layers of a QGIS project, by running a CREATE TABLE statement in the DB manager tool?
Eilin's user avatar
  • 11
5 votes
2 answers
216 views

Use QGIS variables in PostGIS query

How can I build dynamic query to database? In PostGIS database I have table with a column "column3" in which there are values project1, project2, project3, ... project23, project24. I have ...
mapdraftsman's user avatar
3 votes
1 answer
91 views

PostGIS fields of type interval not part of QGIS' field list

I have calculated a flight trajectory dataset from open data which has a couple of fields that can be used in GIS (3.32) without any troubles. but there seems to be a problem with fields of type "...
robert tuw's user avatar
1 vote
1 answer
188 views

Possibility to use st_intersection or st_intersects with more than two layers?

I work on Qgis with DB manager. I have multiple layers on different themes, for example wet areas, ecological corridors, and so on. I would like to know which theme layer is intersecting with my ...
Ambre Arrivé's user avatar
0 votes
1 answer
462 views

Error 2: Importing Shapefile to QGIS DB Manager Failure || PostGIS || PgAdmin

I am new to PostgreSQL, PostGIS, and pgAdmin. I am trying to import a shapefile through the DB Manager (PostGIS --> my specific database) in QGIS. I keep getting an Error 2 message indicating that ...
Alexia Hernandez's user avatar
3 votes
1 answer
752 views

Import an entire geopackage into PostGIS via DB Manager

I am trying to important an entire geopackage to a PostGIS database via the Import Layer/File button in DB Manager. However when I do this only one of the layers in the geopackage gets successfully ...
Ian Moffit's user avatar
6 votes
3 answers
424 views

Adding non-spatial SQLite query to QGIS

How do I load a query of a non-spatial table to be used as a join with a spatial layer? The DB Manager doesn't allow me to load as a layer and I don't want to load the whole table.
Ariel's user avatar
  • 315
4 votes
1 answer
329 views

Create a view in a schema in QGIS DB Manager

When I have created a query in the DBManager window of QGIS I want to save it as a view. When I click on the Create a view button, I'm then prompted for a name. This is all fine, but I want to create ...
Ian Turton's user avatar
  • 82.2k
1 vote
1 answer
373 views

Select a subset of layers from a folder using QGIS

Once a month I get a zip file of 300+ shapefiles made up of lines of individual ship's tracks, and a list of a sub-set of those tracks that I am interested in for analysis and display in QGIS. I want ...
CDEdwards's user avatar
  • 355
1 vote
1 answer
460 views

QGIS - expressions with fields from a related layer

In QGIS I have: a point layer; a table; a relation (Project > Properties > Relations) 1 to many between the points and the table; What I need to do is to set a graduated symbology on the layer ...
HyPhens's user avatar
  • 518
3 votes
1 answer
490 views

Import data to PostGIS DB via QGIS DB Manager is very slow

I am wondering why importing a large vectorlayer (700.000 Polygons) with ogr2ogr tool takes only a few seconds and via QGIS DB Manager more than 1,5 hours. Why is DB Manager so slow with large layers? ...
Enzo Baldini's user avatar
  • 2,347
0 votes
1 answer
281 views

QGIS: update query in db-manager fails

I've created a layer with photos using the tools in QGIS. This yields a table, where each line contains a filepath to a photo. So far so good. But the paths are all absolute. I'd like to share this ...
xof's user avatar
  • 51
3 votes
1 answer
442 views

Importing single-geometry data to postgis

I have several single-geometry-layers (geopackages, shapefiles, temporary layers). Some are LineString, others are Polygon. When I use the DBManager in QGIS to import these layers to the PostGIS ...
Enzo Baldini's user avatar
  • 2,347
2 votes
0 answers
154 views

DB Manager shows a question mark in front of newly created PostGIS layer

I have a polygon layer in a PostGIS database with 16 polygons for the states of Germany. I want to create a new layer for the state of Saxonia in the east of Germany, which has also a different ...
Enzo Baldini's user avatar
  • 2,347
2 votes
1 answer
204 views

Deleted Layer in Geopackage still visible in QGIS

I have created a multitable Geopackage as a Database. After some work, I decided to replace one layer with a different layer, so I created a new layer and deleted the old one via the DB-Manager in ...
C.-F. Vintar's user avatar
  • 1,616
3 votes
0 answers
460 views

How to Multipart to Singlepart a QGIS virtual-layer?

I have a polygon layer I want to explode into single lines. I preferably wish to do so in spatialite since I work almost exclusively in QGIS DB-manager. No matter which function I use the number of ...
maxwhere's user avatar
  • 1,158
0 votes
2 answers
675 views

QGIS DB-Manager Virtual Layers geometry column not showing

Whenever I run a query on project layers that should return a geometry column such as select 1 as id, st_union(geometry) as geometry from layer the column containing the geometry simply doesn't show ...
maxwhere's user avatar
  • 1,158
1 vote
0 answers
226 views

Select features by SQL statement in QGIS

I want to select my features using a SQL sentence but I don't want to create a new layer out of this selection (like you would do in DB manager). Is there a way to achieve this? Some posts hint at ...
franrvr's user avatar
  • 81
1 vote
0 answers
113 views

QGIS Dynamic Virtual Layer as layer coverage of Atlas

Trying to optimize QGIS for my work, I tried to setup a dynamic virtual layer as a layer coverage of my atlas composition, using DB Manager. I join a vector file to a txt file to use one polygon as my ...
Florian Marlière's user avatar
1 vote
2 answers
757 views

QGIS - How do I embed my layer's database connections into the project (rather than DB Manager)? [closed]

Situation: I have projects, lots of projects. They call dozens of layers from the corporate PostGIS. This is largely public data, and the data is referenced via a readonly user account. As it happens, ...
Mat's user avatar
  • 27
2 votes
1 answer
161 views

Loading features from QGIS project layer to PostGIS table

I am in the process of populating a PostGIS database from a bunch of shapefiles loaded in a QGIS project. The field names of the shapefiles don't match those in the database so it is not possible to ...
Techie_Gus's user avatar
  • 2,130
0 votes
0 answers
18 views

db manager shapefile import error [duplicate]

I am trying to import a shapefile using db manager to postgresql. My shapefile has OBJECTID (PRIMARY KEY), ID (STRING FIELD eg: 2543-96-7948 )and several other attributes of type string. When ...
Aman Bagrecha's user avatar
2 votes
2 answers
3k views

Generate a geometry column from latitude and longitude columns with SQL

My table has the columns latitude and longitude in float8 type. I want to generate a geometry column from these two columns. I make the following query in the SQL Window of the DB Manager in QGIS. ...
user111776's user avatar
0 votes
0 answers
48 views

QGIS DBManager Query between Shapefile and PostgreSQL Table

I have an instance of DB Manager running and I have TWO connections present in the left pane of the DB manager form. First connection is to a static shapefile on my local file system Second ...
user9491577's user avatar
3 votes
0 answers
189 views

DB Manager - Oracle DB - Importing vector layer fails prompting "ORA-01741: illegal zero-length identifier" error

When trying to import a vector layer, to an existing Oracle DB connection, using DB Manager core plugin, the operation fails. An error message prompts with "ORA-01741: illegal zero-length ...
EranGeo's user avatar
  • 1,182
3 votes
2 answers
632 views

How to display image from SQLite database at each point? (QGIS)

How do I display images at specific points, through the raster image marker symbology, from a standalone BLOB attribute table (SQLite) loaded into QGIS? To briefly summarise: I have loaded a SQLite ...
Jonathan Gundorph Jansen's user avatar
2 votes
2 answers
1k views

QGIS DBmanager: error creating the SQL layer

i've converted an OSM file from Geofabrik to an SQLite database and loaded it into the QGIS DBManager (QGIS 3.12). I've executed the following SQL query and got a nice table as result. SELECT * FROM ...
DerekUU's user avatar
  • 83
2 votes
1 answer
413 views

Query Geopackage using spatial index in QGIS DB manager

I would like to do spatial join in QGIS DB manager on geopackage database. The query should look like this select * from Soil_C_cadaster a, (select * from Soil_C_cadaster where forest_c is null) b ...
Albine Pro's user avatar
1 vote
0 answers
274 views

Geopackage: Table created with python doesn't show up in QGIS's DB manager

In my python script I want to create a table in a Geopackage that will hold new values to be joined on existing data later. Anyway for table creation I use this: def creaTable(name,sqlcon): c = ...
maxwhere's user avatar
  • 1,158
3 votes
2 answers
654 views

Results not showing in SQL window in DB Manager QGIS 3.12

The following query executes in 0.149 seconds but the results will not display in the box in the bottom half of the DB Manager window, which I assume is the results window (PgAdmin returns 7 rows with ...
Shane Rich's user avatar
2 votes
2 answers
606 views

Column headers ignored when importing .xlsx file to postgres via QGIS DB Manager

I'm importing Excel (.xlsx) files to postgres using the QGIS DB Manager "Import Layer/File..." tool. I've already successfully uploaded very similar Excel files without issue, but for one file in ...
Kristen G.'s user avatar
  • 1,100
4 votes
2 answers
130 views

Join is working in pgAdmin but not in DB Manager

I have two tables tbl1 and tbl2 without geometries which I'm trying to join (and later join result to a table with geometries when I get the first join to work): Simply: select * from tbl1 left join ...
Bera's user avatar
  • 74.8k
1 vote
1 answer
472 views

QGIS DB Manager import Primary Key issue

I have a number of layers that I want to import into a GeoPackage using DB Manager > Import Layer. This data will end up in a process that controls data entry and it needs to comply with corporate ...
Gabriel Van Wyk's user avatar
1 vote
1 answer
514 views

QGIS DBManager: Error Adding Layer

Background: I am running QGIS 3.10.1, PostgreSQL 12.1 and PostGIS 3.0.0 on Windows 10. Using this environment, I am trying to run a simple query in DB Manager: SELECT polygon('((10,20),(25,30),(30, ...
Michael Sheaver's user avatar
1 vote
1 answer
1k views

CREATE TABLE AS with SELECT clause throws error in QGIS DB Manager

I am not able to run the CREATE TABLE AS command in QGIS DB Manager (Win 10, QGIS 3.4.5-Madeira. I am getting the same error in QGIS 2.18 (Win 8). Query execution error on CREATE TEMP VIEW _tview AS ...
Stat-R's user avatar
  • 937
1 vote
0 answers
460 views

QGIS DB Manager won't connect to PostGIS database, but connection "successful" in browser

I have created a spatial database in PGAdmin 4 using PostgreSQL 11. When I connect to the database in QGIS, the connection is successful in browser, but when I go to the DB Manager, it says that I am ...
user123095's user avatar
0 votes
0 answers
323 views

Login as another user in PostGIS using QGIS 2.18 without restart

I have a PostGIS DB with three roles: admin, user and readonly. When I set up a connection to this database via "Add PostGIS layer" this connection is saved as it should be. So I login as admin to ...
MrXsquared's user avatar
  • 34.9k
1 vote
0 answers
372 views

Add CSV file to PostgreSQL using QGIS

I have a CSV file. I would like have this file inside GeoServer. I used "add postgis layers" in QGIS and I added the connection that I have in PgAdmin III. I had the message that the connection is ...
Laura's user avatar
  • 19
2 votes
0 answers
855 views

In QGIS, How to add the database connection in DB Manager via Python [closed]

I am using QGIS 3.0.2 I want to add connection settings in DB Manager via Python script. is it possible ? (I am interested in PostGIS and Oracle Spatial for now) This script will save the ...
Abhijit Gujar's user avatar
1 vote
1 answer
315 views

Run query between PostGIS layer and Shapefile layer with DB Manager

Is it possible to use the DB Manager in QGIS (I am using version 3.4) to run a SQL query between a project layer (e.g. shapefile) and a PostGIS layer? For example, I have a polygon shapefile in my ...
AWGIS's user avatar
  • 3,230
1 vote
0 answers
261 views

How to attach one spatialite database to another in db manager

I'm trying to attach one database to another so that data from it can be used in queries. I'm using ATTACH DATABASE '{full url}' AS {database_name}; No errors actually occur, but when I run PRAGMA ...
user avatar
2 votes
2 answers
1k views

Save query result from QGIS to csv

I have a layer saved to spatialite which I want to query and save part of to csv. Even the query result takes a very long time to load to the browser (2.5M rows) even if I load it without geometry. ...
Dror Bogin's user avatar
  • 3,555
4 votes
1 answer
934 views

Import Shapefile to PostGIS in QGIS' DB Manager without using its ID column

I have a shapefile with a "id" field in which some non-unique ID numbers exist (don't ask). When I try to import this to a PostGIS database using QGIS' DB Manager, it tries to re-use those IDs are ...
bugmenot123's user avatar
  • 10.9k
1 vote
1 answer
271 views

PostGIS layer visible in QGIS Browser panel but displayed as not connected in DBManager

I have added my database as a workshop and you can see the layers in public schema. but whereas in Db manager it is saying NOT CONNECTED. how can I resolve this? this is running on localhost. on ...
Vishnu 's user avatar
0 votes
1 answer
214 views

Unable to connect to connect to Postgres server using DB Manager: unable to convert a QVariant of type 10 to a QMetaType of type 2

Using QGIS 3.0.3 and QGIS 3.1 nightly, I can't connect to a postgres Database using the DB Manager and get the following error: TypeError: unable to convert a QVariant of type 10 to a QMetaType of ...
N.McFadden's user avatar
8 votes
1 answer
783 views

Is there a history/log file for QGIS DB Manager?

When using PostGIS through psql I can look back through the commands I've run in ~/.psql_history. Is there any equivalent log/history file for when I'm connected through QGIS DB Manager? I've ...
RoperMaps's user avatar
  • 2,166
3 votes
2 answers
1k views

Saving queries in QGIS DB Manager

Recently I started using DB Manager to query Spatialite DB. I was happy to see that QGIS DB Manager had a "save query" option, but it seems it saves them only for the current session of QGIS. When I ...
pg85's user avatar
  • 684
3 votes
0 answers
705 views

Oracle connections fail in QGIS 3.0 DB Manager

Oracle connections are working OK in the Browser panel, the Data Source Manager, and the Python console, but any attempt to open a connection in the DB Manager is failing. The database is a fresh 12.1....
Andrew_S's user avatar
  • 187
7 votes
4 answers
556 views

How to disable addition of database name to layer name when adding a vector layer in QGIS?

I am using QGIS version 2.18.14. When I add a vector layer from a file geodatabase, I choose the OpenFileGdb driver and then pick the layers I would like to have on the map. However, the layers are ...
Alex Tereshenkov's user avatar