New answers tagged sql
1
You would need to extract the_geom and distance range from the isochrone function (= data_range) and transform the seconds to minutes.
For example:
WITH isochrone_result AS (
SELECT my_points_table.longitude, my_points_table.latitude, iso.the_geom, iso.data_range / 60 as drive_time
FROM (
SELECT the_geom, ST_X(the_geom) as longitude, ...
4
To answer one of your needs, try using the Aggregate processing tool. You can select one column to group by and use a GUI to determine what aggregate functions to use, renaming columns and adding as many new aggregates as you like.
However it won't discard features or return NULL if a null value is found. You would need an SQL query via Virtual Layers for ...
2
In case not (have you checked the docs?), use a simple (K)NN search:
SELECT CASE WHEN ST_LineLocatePoint(geom, <point>) < 0.5
THEN source
ELSE target
END as node
FROM edges
ORDER BY
geom <-> <point>
LIMIT 1
;
ST_LineLocatePoint in the CASE finds the fraction of line length of the projected point from ...
5
You can use this expression in field calculator:
array_count( array_agg( "fieldnamecontainingstrings",group_by:="fieldnamecontainingstrings"),"fieldnamecontainingstrings")
2
The centroid of a line is not always on the line. What you want is the center along the line, you should look at the ST_LineInterpolatePoint, with a 0.5 parameter you can get the center of the second, if it's what you want.
I'm guessing you want the global center. You should before merge the lines with ST_LineMerge. Then you can split the line with this ...
5
First solution using virtual column and field calculator
Open the attribute table of your layer and click on field calculator icon.
Enter the following expression:
count("your_column", "your_column")
If you close and reopen the table the values will be refreshed.
Second solution to generate a temporary table with unique values and count.
In table ...
1
% is special character (only in second argument to LIKE expression) substituting for zero or more arbitrary characters.
# has no special meaning in SQL LIKE expressions in most databases.
The names used here suggest this expression uses it as its own special character. Probably attribute( @atlas_feature ,'ID') is a single ID, and column IDs is probably a ...
2
This is due to an older version of SQLite distributed with ArcDesktop 10.4 (SQLite version 3.6.21) where 'WITH' statements are not implemented (they were implemented in 3.8.3)
If updating SQLite is not an option (as it isn't for me), any SQL using a 'WITH' statement must be rewritten as a subquery. Subqueries may be used in the SELECT, FROM, WHERE, and JOIN ...
2
In my opinion you do not need any SRID because you are not working with ST_Length_Spheroid().
What @she_weeds is pointing on is indeed correct.
SELECT ws."Network", count(ws."Network"), sum(st_length(ws.geometry))
FROM "Water_Service_Area_Boundaries_Non_Cadastral" AS ws, "W_Mains_DSC_ExclAbandoned" AS wmain
WHERE ST_Intersects(ws.geometry, wmain.geometry)
...
0
I've made changes to have setof geometry
CREATE OR REPLACE FUNCTION get_points(polygon geometry)
RETURNS setof geometry AS
$$
DECLARE
geom_out geometry;
BEGIN
WITH
intervals AS (
SELECT generate_series (0, 9) as steps
),
points as (
SELECT
ST_LineInterpolatePoint(
...
1
You can use array_agg to collect the line IDs at the intersection point.
The number of lines can be more then two, so they can be collected in an array ba array_agg function:
CREATE TABLE junctions as
SELECT
ROW_NUMBER() OVER () AS junction_id,
array_agg(b.road_id) AS road_ids,
ST_Intersection(a.geom, b.geom) AS geom
FROM roads a
INNER JOIN ...
1
You need to specify a condition ON the join, not in the where.
SELECT *
FROM "Lease"
LEFT OUTER JOIN "safe"
ON "Lease"."OBJECTID"="safe"."OBJECTID"
Doing so do the join based on the objectid and keep all rows from the 1st table.
As you did with the where clause, it is doing a full cross-join on both table and then the where keeps only the matching ...
1
Modified from Jaime Burgos. The CROSS APPLY line had the parameter for Oracle. It is different in SQL as included below.
SELECT
i.Name AS FeatureClass
,xVal.value('Name[1]','nvarchar(max)') Field
,xVal.value('DomainName[1]', 'nvarchar(max)') Domain
FROM GDB_ITEMS i JOIN GDB_ITEMTYPES it
ON i.Type = it.UUID
CROSS APPLY i.Definition.nodes('/*/...
0
I ended up using ST_DWithin and was able to retrieve a result:
SELECT
pt.geom, pt.id,
SUM(VC.PERSONS) AS PEOPLE_INJ,
SUM(VC.MOTORIST) AS MOTOR_INJ,
COUNT(*) AS INCIDENT_CT
FROM public.intrsct_pts as pt
JOIN public.valid_collisions as vc
ON ST_DWITHIN(vc.geom, pt.geom, 30.0)
GROUP BY pt.id
ORDER BY incident_ct DESC;
-NOTE: this will return ALL ...
2
This script solves your problem:
WITH tbla AS (SELECT (ST_ExteriorRing(((ST_Dump(geom)).geom))) geom FROM <table_name>), intervals AS (SELECT generate_series (0, 99) as steps) SELECT steps AS stp, ST_LineInterpolatePoint(geom, steps/(SELECT count(steps)::float-1 FROM intervals)) geom FROM tbla, intervals GROUP BY intervals.steps, geom;
Set the ...
0
Answer:
Re: GROUP BY: I posted on GeoNet as well and Esri folks said it was essentially impossible to use 'naked' SQL in queries like that. Why it takes the program down nobody knows, but I learned that that's not a possible solution.
Per @Kirk Kuykendall 's advice, I used the summary statistics tool, using the counties as the case field, and then joined ...
0
As pointed out by @BERA, indexing the tables in combination with using the same coordinate system seems to have reduced the computing time remarkably.
Top 50 recent answers are included
