New answers tagged postgresql
2
You have not provided test data to check the different options for optimizing your query and yet, splitting your query into simple subqueries can lead to a 2-n times optimization, so check my assumption on your data and for this run the query:
WITH
tbla AS (SELECT ST_PointOnSurface(geom) geom FROM sites),
tblb AS (SELECT a.gid, (a.geom) geom, count(*)...
0
The way the function to_postgis works seems to look for the srid in your geometry, not the dtype argument (see _write_postgis here), so maybe it's the problem.
Does your input geometry have a correct srid? Did you created it for example like that:
# Create a GeoDataFrame specifying 'the_point' as the column with the
# geometry data
crs = {'init': 'epsg:4326'...
1
What I often do in these sort of cases is create a class based on a (representative) example of the JSON response (i.e. one that has most or all properties set to a value, rather than null):
Copy the JSON to the clipboard
In Visual Studio, add a new class to your project
Select Edit → Paste special → Paste JSON as classes
You may need to hand-edit some ...
0
You can use a dynamic object in C#. I'm taking a guess here that you are using C# as there is no code posted.
I'm doubting you have an object that exactly matches the ESRI response object, and I doubt you want to take the time to create one. This is what you can use a dynamic object for. In .net core you can use the System.Text.Json namespace to ...
6
You can use st_translate to move the point
SELECT ST_MakeLine(geom, st_translate(geom,0,-500))
FROM liechtenstein.punkte_test;
1
The query that raises an error contains invalid GeoJSON geometry. Programs should not crash and you may consider to create a PostGIS bug report. For making your query to run and give reasonably results it is better to fix the invalid geometry as soon as possible.
I could not test with PostGIS 3.1 but your SQL works for me with PostGIS 3.0. without crash. ...
0
You could have a look at the set of functions offered by pgRouting in the field of VRP http://docs.pgrouting.org/latest/en/VRP-category.html?highlight=vrp. Some additional information can be found here http://osgeo-org.1560.x6.nabble.com/pgrouting-users-Introduction-to-VRP-with-time-windows-Work-done-in-Gsoc-2014-td5197304.html and here https://github.com/...
2
If PostgreSQL is an option for you, it can work this way.
Creating the table with dummy data:
DROP TABLE IF EXISTS distance_matrix_output;
CREATE TABLE distance_matrix_output(
source INTEGER NOT NULL ,
target INTEGER NOT NULL,
distance INTEGER NOT NULL
);
INSERT INTO distance_matrix_output(source, target, distance)
VALUES (1,2,100),
(1,3,...
1
You need to stop thinking of your data as a shapefile and consider it as a collection of features.
So you problem becomes how do I add 2 fixed attributes to a stream of features as I read them from one data source to another data sink. As I explained earlier you can't directly modify the structure of a feature as it is imutable, so you will need to create a ...
1
I am calling addFeatures twice in my code.
this is the first call
featureStore.addFeatures(source.getFeatures());
this is the second call
featureStore1.addFeatures(inputFeatureCollection);
I commented out the second call and the code works without errors!
0
Just update the search path to include public. So you would run
ALTER DATABASE buzzed_api SET search_path TO gc, publIC;
To set it for the current session only run:
SET search_path = public,gc;
2
A simple proximity search with dynamic radius, using ST_DWithin:
SELECT sa.<id>
FROM <service_areas> AS sa
WHERE ST_DWithin(
sa.<geom>,
<customer_geom>,
sa.<radius>
)
;
Note:
sa.<radius> will be treated as unit of CRS (so if your values are given in miles, but the data is referenced in ...
1
Normally that error occurs when QGIS cannot find a column with a unique "primary key". Ensure that the pin column has no duplicates, or add another field with a proper primary key.
See this thread for using the row number
1
You need to construct the point you would like the label to appear from your X and Y columns and then tell the TextSymbolizer to use that geometry to place the label.
I would use something like:
<se:TextSymbolizer>
<se:Geometry>
<ogc:Function name="geomFromWKT">
<ogc:Function name="Concatenate">"...
0
My guess is that querying a single raster is probably very fast, but you are querying 40 years of daily rasters = ~14600 rasters. You can verify this by querying a single date.
What type of data do you need to extract? Here are some ideas:
If you are frequently querying the same time range and need to extract (say) the sum or mean for a particular point, you ...
6
Generally, in SQL you specify the (schema qualified) relation (table) identifier (i.e. public.branches_atms_0601 as part of the FROM expression, and optionally add an ALIAS (e.g. AS br_at), after which you can use that identifier (or ALIAS exclusively, when added) throughout the query.
However, it is required that all relations used in the query are somehow ...
1
Your points table is missing a crucial piece of information: the identifier of the sequential order of points that make up each of the respective higher dimensional geometry (i.e. within the LineString or Polygon). Without knowing the order of points that make up e.g. a road, you can only assemble them in random order! Note that, to construct a Polygon from ...
1
You could use project-wide Macros, which does what you ask for on project load.
A very naive approach, using an input dialog to change the data source table name suffix (the year); _setYear then looks for a loaded layer with the suffix-less base_name and changes its data source (i.e. PG table name) and layer display name to base_name + year:
In <QGIS> ...
0
To convert multipolygons to polygons with ST_Dump
CREATE TABLE polytable AS
SELECT
id, (ST_Dump(w.geometry)).geom::geometry(Polygon,25833) AS geom
FROM
table_multi w
1
If your raster tiles are larger than 1x1, it doesn't make sense to add a geometry column to the same table. ST_PixelAsPoints will generate 900 points for each 30x30 raster tile, which means that you'll either need to store the points as a MultiPoint, or duplicate each raster tile 900 times.
The best options are to
Keep the table with the raster only, and ...
Top 50 recent answers are included
