More

Querying related tables in QGIS

Querying related tables in QGIS


I have a parcel layer and a table of property sales. I have the two tables related (it is a one-to-many relationship) in a QGIS project so that when you click on a parcel you see all the related sales information. Is there a way to query that related sales table and have it select the appropriate parcels directly in QGIS?

I have come up with a round-about way where I create a view in the Spatialite DB that the data is stored in but it is asking my end user a bit too much to be able to change the views parameters every time he needs a new sales map.


I have the same issue. If find an alternative solution based on a simple selection… (Duplicate issue i've made)

From the parent table, I retrieve a list of ID's (PK) and in the child table, I set a filter based on my list of ID's to my FK.

Important notice, I do not use the relationship, only a selection based on PK FK. There is an important performance issue on large tables.

Here a snippet.

li_id = [] layers = QgsMapLayerRegistry.instance().mapLayers() for name, layer in layers.iteritems(): if name.lower().startswith('NAME OF MY PARENT TABLE'): features = layer.selectedFeatures() for f in features: li_id.append(str(f.attribute('F_ID'))) cLayer = iface.mapCanvas().currentLayer() # (CHILD TABLE SELECTED IN TOC) if len(li_id) ==1: cWhere = """"F_ID" = '{0}'""".format(li_id[0]) else: cWhere= """"F_ID" in {0}""".format(tuple(li_id)) expr = QgsExpression(cWhere) it = cLayer.getFeatures( QgsFeatureRequest( expr ) ) ids = [i.id() for i in it] cLayer.setSelectedFeatures( ids ) iface.showAttributeTable(iface.activeLayer())

Identifying Europe's major road exits using QGIS

Dataset from EuroGeographic and Global Roads Open Access Data Set (gROADS) have been downloaded.

The goal is to identify the exits (geocoordinate points) of highways and primary roads (gROADS refers to them as FClass Functional Class 1=Highway, 2=Primary). The shape data is seen and visible with software tool QGIS, but the roads are but collections of lines.

The problem is the data set is not obvious to parse. The EGM 2021 Dataspecification has a Data Dictionary under the transportation section data tables a ‘Feature class name’ EXITC with code AQ090 which are not found or, if present, sometimes of limited data. So for example, country code IT has no Exitc dbf file. Yet Google and Openstreetmap have these elements.

Consulting the database files (EuroGeographic) for ROADL which are lines, entries have names that are repeated, thus I assume that these are segments, and thus the end-points of the segments would correspond to entry/exit points.

Yet I cannot fathom how these can be extracted from this data example.

How can these exits be obtained?

One Answer

[This answer is not a direct answer to the question, but a path to a solution]

Openstreetmap has this data. geofabrik.de has various regional downloads the continental ones are rather huge, so I recommend to first examine the linked json file.

The downloaded archived have shape files, road being the family of interest, which then can be filtered out. Query

will generated expected data. Note: motorway is the key for the actual road and line geometry, while appending _link provides the nodes that access the road for these major roads.


Adding attributes of points to attribut table of lines in QGIS

I want to add only the names of my vertices (from line intersection, duplicates deleted) to the attributtable of my lines (like start and end node). Every line has only two vertices.

geom_to_wkt(start_point($geometry)) –> gives me the coordinates (I need the name) of my real nodes not of the vertices

"add by position" only transfers the name of one vertices (but I need both)

One Answer

In QGIS I can suggest using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer.

Let's assume we have two layers 'points_layer' (red) and 'lines_layer' (grey) respectively, see image below.

With the following query, it is possible to add only the names of my vertices (from line intersection, duplicates deleted) to the attributtable of my lines (like start and end node).


QGIS download and installation

QGIS can be installed on Windows, Mac OS X, Unix, Linux, and Android operating systems, making it a very flexible software package. Both the binary installers and source code can be downloaded from download.qgis.org. In this section, we will briefly cover how to install QGIS on Windows, Mac OS X, and Ubuntu Linux. For the most up-to-date installation instructions, refer to the QGIS website.

Installing QGIS on Windows

For Windows, there are two installation options, which are as follows:

QGIS Standalone Installer : The standalone installer installs the binary version of QGIS and the Geographic Resource Analysis Support System ( GRASS ) using a standard Windows installation tool. You should choose this option if you want an easy installation experience of QGIS.

OSGeo4W Network Installer : This provides you with the opportunity to download either the binary or source code version of QGIS, as well as experimental releases of QGIS. Additionally, the OSGeo4W installer allows you to install other open source tools and their dependencies.

Installing QGIS on Mac OS X

To install QGIS on Mac OS X, the Geospatial Data Abstraction Library ( GDAL ) framework and matplotlib Python module must be installed first, followed by the QGIS installation. The installation files for GDAL, matplotlib, and QGIS are available at http://www.kyngchaos.com/software/qgis.

Installing QGIS on Ubuntu Linux

There are two options when installing QGIS on Ubuntu: installing QGIS only, or installing QGIS as well as other FOSSGIS packages. Either of these methods requires the use of the command line, sudo rights, and the apt-get package manager.

Installing QGIS only

Depending on whether you want to install a stable release or an experimental release, you will need to add the appropriate repository to the /etc/apt/sources.list file.

With sudo access, edit /etc/apt/sources.list and add the following line to install the current stable release or current release's source code respectively:

Depending on the release version of Ubuntu you are using, you will need to specify the release name as trusty , saucy , or precise . For the latest list of QGIS releases for Ubuntu versions, visit download.qgis.org.

With the appropriate repository added, you can proceed with the QGIS installation by running the following commands:

To install the GRASS plugin (recommended), install the optional package by running this command:

Installing QGIS and other FOSSGIS Packages

The ubuntugis project installs QGIS and other FOSSGIS packages, such as GRASS on Ubuntu. To install the ubuntugis package, remove the http://qgis.org/debian lines from the /etc/apt/sources.list file, and run the following commands:

QGIS is also available for Android. We have not provided detailed installation instructions because it is in alpha testing at the moment. However, there are plans to have a normalized installation process in a future release. You can find more information about this at http://hub.qgis.org/projects/android-qgis.

A related app has recently been announced and it is named QField for QGIS. For a short time, it was named QGIS Mobile. It is described as a field data capture and management app that is compatible with QGIS. At the time of writing this, it was in invite-only alpha testing. It is eventually expected to be available in the Android Play Store. You can find more information on this app at http://www.opengis.ch/tech-blog/.


6.2.5. Try Yourself Convert Layers’ CRS¶

Because we are going to be measuring distances within our layers, we need to change the layers’ CRS. To do this, we need to select each layer in turn, save the layer to a new one with our new projection, then import that new layer into our map.

You have many different options, e.g. you can export each layer as an ESRI Shapefile format dataset, you can append the layers to an existing GeoPackage file, or you can create another GeoPackage file and fill it with the new reprojected layers. We will show the last option, so the training_data.gpkg will remain clean. Feel free to choose the best workflow for yourself.

In this example, we are using the WGS 84 / UTM zone 34S CRS, but you should use a UTM CRS which is more appropriate for your region.

Right click the roads layer in the Layers panel

Click Export –> Save Features As…

In the Save Vector Layer As dialog choose GeoPackage as Format

Click on … for the File name , and name the new GeoPackage vector_analysis

Change the Layer name to roads_34S

Change the CRS to WGS 84 / UTM zone 34S

This will create the new GeoPackage database and add the roads_34S layer.

Repeat this process for each layer, creating a new layer in the vector_analysis.gpkg GeoPackage file with _34S appended to the original name and removing each of the old layers from the project.

When you choose to save a layer to an existing GeoPackage, QGIS will append that layer to the GeoPackage.

Once you have completed the process for all the layers, right click on any layer and click Zoom to layer extent to focus the map to the area of interest.

Now that we have converted OSM data to a UTM projection, we can begin our calculations.


3 Answers 3

Couple of options from the comments on the mssql_query() manual page

  • SELECT CAST(field1 AS TEXT) AS field1 FROM table
  • Chang the version in /etc/freetds.conf from 4.2 to 8.0 (if the PHP server is *nix)
  • Avoid SELECT * queries

Plenty more if you search ntext on that page.

Here are some things you might need to know:

Install mssql support for Debian (Lenny/Squeeze):

apt-get install php5-sybase

When you got this error message: "Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier."


Results

A total of 10,989 school districts and 7,479 vape shop addresses were included in the final analysis. Of these school districts, 73.3% (8,053) had no vape shops, 13.7% (1,502) had one vape shop, 7.4% (818) had two, and 5.6% (616) had three or more vape shops within them.

Vape shop density in the school districts was significantly positively associated with percentage Asian and Black or African American populations, with density increasing as a function of both races based on unadjusted results. Vape shop density in the school districts was significantly negatively associated with percentage AIAN and percentage White population, with density decreasing as a function of both races based on unadjusted results. Hispanic population and poverty were not significantly associated with the density of vape shops in school districts ( Figure 1A-F , Supplemental Table S1).

Figure 1 Relationship Between Vape Shop Density (per 100,000 Population) and Socioeconomic Variables in School Districts of the Conterminous United States During 2018

NOTE: Depicted are the unadjusted model estimates (dark line) and confidence limits (95% confidence interval shaded area) from separate quasi-Poisson generalized linear models analyzing vape shop density associations with races/ethnicities (A-E) and poverty (F). Values presented here were back-transformed from the original log-link function estimated model coefficients. Results except those in D and F were statistically significant (α = .05). Note that the x and y axes differ for each separate plot to allow for better visual understanding of the trend in each separate model.

Ratio of vape shops to schools in the school districts was significantly positively associated with percentage Asian and Black or African American populations, with ratio increasing as a function of both races based on unadjusted results. Ratio of vape shops to schools was significantly negatively associated with percentage AIAN, percentage White populations, and poverty, with the ratio decreasing as a function of these factors based on unadjusted results. Hispanic population was not significantly associated with the ratio of vape shops to schools in school districts ( Figure 2A-F , Supplemental Table S1).

Figure 2 Relationship Between the Ratio of Vape Shops to Schools (per 100,000 Population) and Socioeconomic Variables in School Districts of the Conterminous United States During 2018

NOTE: Depicted are the unadjusted model estimates (dark line) and confidence limits (95% confidence interval shaded area) from separate quasi-Poisson generalized linear models analyzing ratio of vape shop to school associations with races/ethnicities (A-E) and poverty (F). Values presented here were back transformed from the original log-link function estimated model coefficients. Results except those in D were statistically significant (α = .05). Note that the x and y axes differ for each separate plot to allow for better visual understanding of the trend in each separate model.

Median distance of schools to vape shops in the school districts was significantly negatively associated with percentage Asian, Black or African American, and Hispanic or Latino populations, with vape shops in closer proximity to schools in districts with higher proportions of these populations based on unadjusted results. Median distance of schools to vape shops was significantly positively associated with percentage White population and poverty. Distance to vape shops increased with percentage White population and population in poverty ( Figure 3A-F , Supplemental Table S1).

Figure 3 Relationship Between Distance From Schools to Vape Shops (km) and Socioeconomic Variables in School Districts of the Conterminous United States During 2018

NOTE: Depicted are the unadjusted model estimates (dark line) and confidence limits (95% confidence interval shaded area) from separate quasi-Poisson generalized linear models analyzing median distance between schools and vape shops and associations with race/ethnicity (A-E) and poverty (F). Values presented here were back transformed from the original log-link function estimated model coefficients. All results were statistically significant (α = .05). Note that the x and y axes differ for each separate plot to allow for better visual understanding of the trend in each separate model.


Methods

Data quality

The list of hospitals in Australia distributed by the Australian Institute of Health and Welfare on the MyHospitals website contained, as of April 2019, all 690 public hospitals in Australia and 321 private hospitals 11 however, some private hospitals might have been missing since participation in MyHospitals by private hospitals is voluntary. Further details on data quality assurance are available online 12 . Verification of the geographic coordinates of 10 randomly sampled hospitals in each Australian state and territory (where present) did not indicate any inaccuracies.

OpenStreetMap data for Australia, updated daily, is available for download 13 . Previous work determined that in January 2016, coverage of the Australian road network by OpenStreetMap was 94% 14 . Global coverage increased from 83% in January 2016 to 89% in May 2017 14 country-specific increases were not reported but coverage of the Australian road network is likely to have improved as well.

Computation of mesh block centroids

MBs for the different Australian states and territories were downloaded in shapefile format 15 to a personal computer (Intel Core i7-6600U CPU at 2.60 GHz, 16GB of RAM). The QGIS geographic information system, version 3.6.2, was used to merge these MB files and compute the centroids of all spatial MBs. The MB centroids were exported as a comma-separated values (csv) file and merged with the table of population counts based on MB code.

Computation of travel times between mesh blocks and hospitals

OpenStreetMap data for Australia 13 was used to initiate a local instance of the Open Source Routing Machine (OSRM) HTTP server (version 5.16.0 on Windows 10). The table service of the OSRM server was used to query the car travel time of the fastest route between each centroid of spatial, inhabited, MBs and each hospital in Australia. The OSRM server does take into account possible ferry services but may not return a valid travel time if no route is found (e.g. there are currently no passenger ferries to King Island in Tasmania where the King Island District Hospital and Health Centre is located). The OSRM server supports the concurrent processing of multiple requests and the total processing time for creating the full travel time matrix between MBs and hospitals was approximately 10 hours. Given the travel times matrix between all MBs and hospitals it was possible to compute the shortest travel time between each MB and any hospital.

Computation of Travel Times Between SA2s and Hospitals

Averaging across MBs in each SA2, weighted by population, determined the travel times matrix between SA2s and hospitals, as well as the average shortest travel time to a hospital for residents of a SA2. These steps were performed using Python version 3.6.4.


Exploration

After listing out the geospatial problems and the ideas, we have to make sure of prioritizing things that needs prior attention. Here prioritization plays a significant role. Because the organization can have the strengths and weaknesses, so we have to identify them before initiating the innovation. We must have the ability to look out their potential in managing the cost and utilizing the resources to explore a new strategy or producing a device. Hereabouts we also need to look out for the probability of the need for innovation. The innovations would be making a real-time map, solving a spatial problem, or collecting statistical attributes. This can be cleared out by prioritizing the things.


Background

Attention-deficit/hyperactivity disorder (ADHD) is a neurodevelopmental disorder that affects a significant proportion of individuals across their lifespan, for which there are effective, evidence based treatments [1]. Mental health service provision in higher income countries is separate for children and adults, and the transition often occurs at a key developmental stage [2, 3]. Epidemiological studies show an estimated prevalence of ADHD of 5–6% in children, and 3–4% in adults [4,5,6,7]. The number of young people with ADHD graduating from children’s services has increased rapidly, because of a rise in childhood prescription rates, [8] and studies have found that many young people with ADHD are likely to need continued care into adulthood [9,10,11]. Therefore providing a supported transition for this group in line with clinical guidelines is important [12, 13]. However, provision for adults with ADHD remains relatively scarce across the world [14] and is known to be patchy and difficult to access in the United Kingdom (UK) [15].

Atlases of Health, which map international health service provision using government and expert sources, are well-established tools designed to provide objective and reliable information on healthcare service provision [16,17,18]. The European Service Mapping Schedule [19] is a survey instrument for the description and classification of mental health services. It was adapted by Signorini et al. [20], and used to survey child psychiatry representatives on the characteristics of child and adolescent mental health services (CAMHS) across the European Union (EU). While valuable, these tools often fail to triangulate government and expert reports of service provision with the experience of service users and clinicians in practice. The general focus also means that they may not capture condition specific information.

The Atlas of Variation series [21] uses routinely available data and consultation with clinical experts, to provide government reports (with maps, charts and time-series data) on provision and patient outcomes for a selection of health topics [22]. However, to date, ADHD services and outcomes have not been mapped. The information provided is also highly complex, difficult for lay readers to understand and does not include accounts from service users and clinicians. Independent regulators such as the Care Quality Commission use inspection methodology including consultations with staff and service users and observing clinical practice to provide detailed reports on the state of care [23]. Findings are reported in a format that is accessible to a range of stakeholders. However, this is a resource intensive process, and most specialisms are not identified separately from community mental health, which makes it impossible to learn about adult ADHD service provision from these reports [23]. Without a national audit aimed specifically at mapping adult ADHD provision, drawing on a range of stakeholder sources, it is difficult to quantify and address current gaps in healthcare and make the case for appropriate change.

In a recent survey of every National Health Service (NHS) mental health trust in England, senior health professionals were asked to provide information on transition protocols, pathways and commissioned services for ADHD [24]. Over two thirds of NHS Trusts responded (68%). The survey was designed to be completed by a senior healthcare professional within the trust and it is unknown whether non-response from 17 trusts reflects reluctance to report on gaps in services or other reasons for non-response such a lack of time or personnel. Less than half of the responding mental health trusts in England offered specialist provision for adults with ADHD and less than a third had specific commissioning arrangements for this group [24]. In a separate survey, all healthcare professionals working in child and adult health services in the East Midlands region of England, were asked about transitional health services for young people with ADHD [25]. The overwhelming majority of respondents reported a lack of provision [25]. Despite a relatively low response rate (19%), surveying all staff resulted in responses from a variety of professionals working with people with ADHD including psychiatrists, managers, nurses and paediatricians. This method, although more resource intensive and limited to a smaller geographic area, included perspectives of clinicians working daily with patients. It is possible however, that they will not have daily experience of service provision in practice, while the pressures of managing a resource strapped service may conflict with straightforward reporting.

Our recent systematic review of qualitative research about transition into adult ADHD services found that a lack of available information about adult ADHD services created difficulties in accessing treatment [26]. People with ADHD reported they did not know where to access treatment [27], while some clinicians reported difficulties in finding an adult service to refer patients on to [28]. This work indicates the importance of information about where services for ADHD are, what they offer and how to access them. Methods used to map ADHD services need to collect data that is relevant and accessible to patients and clinicians as well as service providers and commissioners. Different stakeholders are likely to have different perspectives on what is as well as what needs to be available and it would be interesting to explore differences between provider’s reports on service availability and patient experiences of provision.

To extend and expand the findings from previous research [20, 24, 25], we piloted and refined a multi-informant, multi-source methodology to map adult ADHD provision in the UK. This paper describes the seven step approach that we developed. The methods used are intended to meet current needs for national service data specific to ADHD and to enable a comparison of differences in reported information by different stakeholder groups.


Watch the video: QGIS 3D Map using 3D View Version