New answers tagged sql
1
Unfortunately in this case, ST_Segmentize only inserts vertices into the linear component of a geometry, and only when the distance between consecutive points is longer than the given max_segment_length - a point dump will contain the initial vertices as well [e.g. observable in the respective image in @jgrocha's answer].
If that is undesirable, use a ...
2
Using QGIS
Since you added QGIS tag to your question, you can use the very simple processing tool called Points along geometry.
It allows you to create points along the line.
Using Postgis
Using Postgis, you can use the query you already wrote.To make it more usable, I've created another (temporary) table with the result.
My query is:
create table outros....
1
A solution using a case
SELECT CASE
WHEN i = 'other' THEN i_o
ELSE i
END as i
FROM your_table
You can mostly do the same without any SQL using QGIS expressions for labeling if using Desktop GIS like QGIS but I do not know your usage context (web or desktop, software used,...)
0
First off, never use string concatenation when you're building SQL query strings. It's a bad habit, as it'll leave you vulnerable to SQL injections if you ever give people access to your code, say, via a website.
In your example, you're constructing a list from a ComboBox to populate an IN clause. You'd want to create a variable that concatenates those ...
0
I think this SDO_GEOMETRY solution could be adapted to work with SDE.ST_GEOMETRY.
Replace cust_pointn with st_pointn.
Replace sdo_util.getnumvertices with st_numpoints.
Replace .sdo_point.x with st_x.
--Custom PointN fucntion: https://gis.stackexchange.com/a/412038/135445
create or replace function cust_pointn (g sdo_geometry, n number)
return sdo_geometry
...
2
Layer name with space character is a so called delimited identifier in SQL. See for example https://www.ibm.com/docs/en/informix-servers/12.10?topic=identifier-delimited-identifiers.
Delimited identifiers must be enclosed between double quotes "delimited identifier". To prevent the truncation of the -sql parameter in ogr2ogr that must also appear ...
0
I just had the same problem. And actually the problem was as it was written in the title "Can't create fields of type Binary on shapefile". So I just deleted the row "Geometry" type binary... That doesn't affect the geometry of my layer. And the export worked. Voilà!
2
With using the following SQL expression:
SELECT ST_Distance("random_points_test"."geometry", "poly_test"."geometry")
FROM "random_points_test", "poly_test"
I was able to recreate the same error
To overcome it, there are several suggestions:
use an alias e.g. AS dist i.e. ST_Distance("...
7
You need to use a QGIS Python Macro.
QGIS Expression is not supported in the Query Builder.
Go in Project properties then Macros, the code is under the screenshot :
Python code to use, you need to replace the layer_id variable :
from qgis.core import QgsProject
def openProject():
project = QgsProject.instance()
base_name = project.baseName()
...
1
What you need is the crosstab function from the tablefunc extension.
First install the extension:
create extension tablefunc;
Then you can use the crosstab function like this:
select * from
crosstab('select id, name, value from your_table order by 1,2',
$$values ('name1'), ('name2'), ('name3')$$)
as ct(id int, name1 int, name2 int, name3 int);
...
4
The easiest way would be to use a virtual layer :
Go to : > Layer > Add Layer > Add/Edit Virtual Layer...
In the "Create virtual Layer" dialog click on import and choose your
point dataset
In the query part make an SQL query like this one : SELECT year(ACQ_DATE) as year, COUNT(ACQ_DATE) as 'number of incidents' FROM YOUR_LAYER GROUP BY ...
2
Use this expression. Normalle, if using field calculator, it calculates the same list (each year + number of entries per year) separately for each feature. To avoid that, simply select one feature and then run field calculator with the box checked next to Only update 1 selected features.
If your date is indeed formatted as date (not as text), instead of left ...
3
This SQL query should do what you're asking:
select
fieldA, fieldB, row_number() over(partition by fieldA order by fieldB) as fieldC
from
"your_table"
order by
fieldA, fieldB;
1
There is no equivalent out of the box. But implementing such a function is easy with a bit of PL/SQL:
create or replace function st_pointn (g sdo_geometry, n number)
return sdo_geometry
as
begin
return (
sdo_geometry (
2001,
g.sdo_srid,
sdo_point_type (
g.sdo_ordinates((n-1) * 2 + 1),
g.sdo_ordinates((n-1) * 2 + 2),
...
1
You can do below to get vertex at an index from a linestring in Oracle SDO_GEOMETRY
SELECT
l.lid,
t.X,
t.Y,
t.id
FROM
tblLines l,
TABLE(SDO_UTIL.GETVERTICES(l.geometry)) t
where
t.id= <<Index of the vertex>>
ORDER BY
l.lId,
t.id;
Top 50 recent answers are included