4

For get all the visited grid cells in order, even if the grid cell is revisited, I wrote the SQL query below : SELECT tr.tr_id, ce.grid_id FROM cells ce, traj tr ORDER BY ST_LineLocatePoint( tr.traj_path, ST_CENTROID( (ST_DUMP( ST_Intersection(ce.coordinates, tr.traj_path) )).geom ) ); Explanations : The big part is ...


3

You get an entry for each voter because you are grouping on the voter column as well, which results in groups that are all single rows. And you cannot include voter_name in the query unless you either aggregate on the column or build groups that include the column, which means that you have to add one more query level to achieve your objective. Here are a ...


2

You can use UNION to combine the queries. Your server can then concat the binaries before sending them out. I found out that doing this in SQL took to long and increased the compute time to much. You can stack the UNION queries for more objects. Here is an example of the query I used: ( SELECT 'road' as name, ST_AsMVT(q, 'road', 4096, 'geom') as tile FROM ...


2

I finally found out why all this. It was because of an old style that was saved in the database and that referred to an older Version of the layer, where some attributes where missing before. In the new Version of the layer all new attributes where added at the end. Overwriting the style using QGIS 2.14 solved the problem.


2

First advice would be to create a ST_MapAlgebra() custom callback function instead of converting everything to polygon and then back to raster. ST_MapAlgebra() will iterate over all the raster pixels assigning the value your function computed from the vector coverage. You can extract any king of metric with that. The function has to return only one value ...


2

If I correctly understood the translation of your question, try running this script: If these are horizontal lines, then: create table line_to_poly as WITH closed_contours AS ( SELECT ((ST_Dump(ST_Polygonize(geom))).geom) FROM sourse_table) SELECT (ST_Dump(ST_Union(geom))).geom FROM closed_contours If polygons, then: create table un_poly as ...


2

I would go with... SELECT Box2D(geom) as bounds, ST_YMax(bounds) as ymax, ST_YMin(bounds) as ymin, ST_XMax(bounds) as xmax, ST_XMin(bounds) as xmin FROM nation_shapes WHERE (condition) ...as PostgreSQL's SQL engine and query planner is smart enough to figure things out and not duplicate work. If you want to not return the bbox from ...


2

Unnest is working when I try: select id, coalesce(sub.level,0) maxlevel from sometable left join ( select distinct on (1) unnest(ancestor) id2, level from sometable order by 1,2 DESC) sub on sometable.id = sub.id2


1

I don't know a simple way to do this, you will need extra step. The first step would be to brake your line into parts that are in the same direction (left or right angles). Then for each of your parts you can assesed its "complexity" for exemple by comparing its length to the length of the line first_point - last_point (I've seen a paper that called that "...


1

It is actually not a bug. The saving of the password does work fine in QGIS 3.6. After storing the password under basic tab in the connection to PostGIS window, you will notice you will no longer get asked a password when you connect to the database, however when you open the QGIS projects the password will pop up again. So go to individual layers and using ...


1

In situations like this where the intent is to select existing geometry rather than construct new geometry it's preferable to avoid using overlay operations (like ST_Union) since they are less performant and can alter data slightly. Instead, it's better to use spatial relationships. This is a classic case of "finding things which aren't related to other ...


1

You could use ST_Union to generate one big multipolygon and then ST_Dump to separate its component polygons into separate records. If you then needed to get attributes from the original data you could join based on geometry.


1

In the QGIS options, data sources tab there is a "Representation for NULL values" setting that seem to be set to NULL by default, you could try to alter this setting (erasing the NULL and let it empty ?)


1

This error can occur when you have a mixed installations of x86 and x64. Did you ever installed x86 versions of PostgreSQL and/or PostGIS before? try removing all versions and install from link here again.


1

I finally found the solution. Fix the data source. Where it says localhost, put the IP address. Do this for every layer, then resave the QGIS project.


1

As I needed only the cost and not the entire path, I used pgr_dijkstraCost to calculate the costs and now it takes only 2 minutes for more than a million records. I have also added another column to user information to store the nearest osm node and that made my query much faster than expected. Below is the query. INSERT INTO "UserTravelTime" ("FromId", "...


1

The eps distance is the maximum distance between points in the cluster, not the maximum width of the entire cluster. So if you have points A, B, and C, as long as each point is within the eps distance of one other point, then it gets included in the cluster. If the eps distance was 1 km, A could be within 1 km of B, and C can be within 1 km of B, but A can ...


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