Error with generate_series The 2019 Stack Overflow Developer Survey Results Are InHow to...
What is the meaning of Triage in Cybersec world?
Are there any other methods to apply to solving simultaneous equations?
Time travel alters history but people keep saying nothing's changed
How to type this arrow in math mode?
What does "fetching by region is not available for SAM files" means?
Have you ever entered Singapore using a different passport or name?
Can you compress metal and what would be the consequences?
Can a rogue use sneak attack with weapons that have the thrown property even if they are not thrown?
Why isn't airport relocation done gradually?
Origin of "cooter" meaning "vagina"
Shouldn't "much" here be used instead of "more"?
Resizing object distorts it (Illustrator CC 2018)
Are there incongruent pythagorean triangles with the same perimeter and same area?
For what reasons would an animal species NOT cross a *horizontal* land bridge?
"as much details as you can remember"
How to save as into a customized destination on macOS?
What is the most effective way of iterating a std::vector and why?
How to deal with fear of taking dependencies
How to manage monthly salary
Am I thawing this London Broil safely?
Why is the maximum length of OpenWrt’s root password 8 characters?
Multiply Two Integer Polynomials
Did Section 31 appear in Star Trek: The Next Generation?
The difference between dialogue marks
Error with generate_series
The 2019 Stack Overflow Developer Survey Results Are InHow to insert 3d line into postgis database?How to create random points in a polygon in PostGIScan't geocode with PostGIS 2.1.1Function get_partition(geometry, unknown) does not exist - Nominatim tiger data importPython GDAL open dataset in PostGIS with quotationsHow to deal with “ERROR: GEOSUnaryUnion: TopologyException: Self-intersection”?PostgreSQL Queries pgAdmin versus Function callProblem with insert into PostGIS view table via QGISCross-schema function call with PostGIS dependenciesVery slow loading of PostGIS raster layer in qGIS
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I`d like to create a Hex Grid using Hex grid algorithm for PostGIS (https://medium.com/@goldrydigital/hex-grid-algorithm-for-postgis-4ac45f61d093)
DO $$
DECLARE
_curs CURSOR FOR SELECT geom3857 FROM nrw;
_table TEXT := 'nrw_hx_10k';
_srid INTEGER := 3857;
_height NUMERIC := 10000;
_width NUMERIC := _height * 0.866;
_geom GEOMETRY;
_hx GEOMETRY := ST_GeomFromText(
FORMAT('POLYGON((0 0, %s %s, %s %s, %s %s, %s %s, %s %s, 0 0))',
(_width * 0.5), (_height * 0.25),
(_width * 0.5), (_height * 0.75),
0 , _height,
(_width * -0.5), (_height * 0.75),
(_width * -0.5), (_height * 0.25)
), _srid);
BEGIN
CREATE TEMP TABLE hx_tmp (geom GEOMETRY(POLYGON));
OPEN _curs;
LOOP
FETCH
_curs INTO _geom;
EXIT WHEN NOT FOUND;
INSERT INTO hx_tmp
SELECT
ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON) geom
FROM
generate_series(
(st_xmin(_geom) / _width)::INTEGER * _width - _width,
(st_xmax(_geom) / _width)::INTEGER * _width + _width,
_width) x_series,
generate_series(
(st_ymin(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) - _height,
(st_ymax(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) + _height,
_height * 1.5) y_series
WHERE
ST_Intersects(ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON), _geom);
INSERT INTO hx_tmp
SELECT ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON) geom
FROM
generate_series(
(st_xmin(_geom) / _width)::INTEGER * _width - (_width * 1.5),
(st_xmax(_geom) / _width)::INTEGER * _width + _width,
_width) x_series,
generate_series(
(st_ymin(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) - (_height * 1.75),
(st_ymax(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) + _height,
_height * 1.5) y_series
WHERE
ST_Intersects(ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON), _geom);
END LOOP;
CLOSE _curs;
CREATE INDEX sidx_hx_tmp_geom ON hx_tmp USING GIST (geom);
EXECUTE 'DROP TABLE IF EXISTS '|| _table;
EXECUTE 'CREATE TABLE '|| _table ||' (geom GEOMETRY(POLYGON, '|| _srid ||'))';
EXECUTE 'INSERT INTO '|| _table ||' SELECT * FROM hx_tmp GROUP BY geom';
EXECUTE 'CREATE INDEX sidx_'|| _table ||'_geom ON '|| _table ||' USING GIST (geom)';
DROP TABLE IF EXISTS hx_tmp;
END $$;
When I try to use the function, I get the following error:
ERROR: function generate_series(numeric, numeric, numeric) does not exist
LINE 5: generate_series(
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: INSERT INTO hx_tmp
SELECT
ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON) geom
FROM
generate_series(
(st_xmin(_geom) / _width)::INTEGER * _width - _width,
(st_xmax(_geom) / _width)::INTEGER * _width + _width,
_width) x_series,
generate_series(
(st_ymin(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) - _height,
(st_ymax(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) + _height,
_height * 1.5) y_series
WHERE
ST_Intersects(ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON), _geom)
CONTEXT: PL/pgSQL function inline_code_block line 27 at SQL statement
********** Error **********
ERROR: function generate_series(numeric, numeric, numeric) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Context: PL/pgSQL function inline_code_block line 27 at SQL statement
postgis postgresql function
New contributor
add a comment |
I`d like to create a Hex Grid using Hex grid algorithm for PostGIS (https://medium.com/@goldrydigital/hex-grid-algorithm-for-postgis-4ac45f61d093)
DO $$
DECLARE
_curs CURSOR FOR SELECT geom3857 FROM nrw;
_table TEXT := 'nrw_hx_10k';
_srid INTEGER := 3857;
_height NUMERIC := 10000;
_width NUMERIC := _height * 0.866;
_geom GEOMETRY;
_hx GEOMETRY := ST_GeomFromText(
FORMAT('POLYGON((0 0, %s %s, %s %s, %s %s, %s %s, %s %s, 0 0))',
(_width * 0.5), (_height * 0.25),
(_width * 0.5), (_height * 0.75),
0 , _height,
(_width * -0.5), (_height * 0.75),
(_width * -0.5), (_height * 0.25)
), _srid);
BEGIN
CREATE TEMP TABLE hx_tmp (geom GEOMETRY(POLYGON));
OPEN _curs;
LOOP
FETCH
_curs INTO _geom;
EXIT WHEN NOT FOUND;
INSERT INTO hx_tmp
SELECT
ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON) geom
FROM
generate_series(
(st_xmin(_geom) / _width)::INTEGER * _width - _width,
(st_xmax(_geom) / _width)::INTEGER * _width + _width,
_width) x_series,
generate_series(
(st_ymin(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) - _height,
(st_ymax(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) + _height,
_height * 1.5) y_series
WHERE
ST_Intersects(ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON), _geom);
INSERT INTO hx_tmp
SELECT ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON) geom
FROM
generate_series(
(st_xmin(_geom) / _width)::INTEGER * _width - (_width * 1.5),
(st_xmax(_geom) / _width)::INTEGER * _width + _width,
_width) x_series,
generate_series(
(st_ymin(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) - (_height * 1.75),
(st_ymax(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) + _height,
_height * 1.5) y_series
WHERE
ST_Intersects(ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON), _geom);
END LOOP;
CLOSE _curs;
CREATE INDEX sidx_hx_tmp_geom ON hx_tmp USING GIST (geom);
EXECUTE 'DROP TABLE IF EXISTS '|| _table;
EXECUTE 'CREATE TABLE '|| _table ||' (geom GEOMETRY(POLYGON, '|| _srid ||'))';
EXECUTE 'INSERT INTO '|| _table ||' SELECT * FROM hx_tmp GROUP BY geom';
EXECUTE 'CREATE INDEX sidx_'|| _table ||'_geom ON '|| _table ||' USING GIST (geom)';
DROP TABLE IF EXISTS hx_tmp;
END $$;
When I try to use the function, I get the following error:
ERROR: function generate_series(numeric, numeric, numeric) does not exist
LINE 5: generate_series(
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: INSERT INTO hx_tmp
SELECT
ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON) geom
FROM
generate_series(
(st_xmin(_geom) / _width)::INTEGER * _width - _width,
(st_xmax(_geom) / _width)::INTEGER * _width + _width,
_width) x_series,
generate_series(
(st_ymin(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) - _height,
(st_ymax(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) + _height,
_height * 1.5) y_series
WHERE
ST_Intersects(ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON), _geom)
CONTEXT: PL/pgSQL function inline_code_block line 27 at SQL statement
********** Error **********
ERROR: function generate_series(numeric, numeric, numeric) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Context: PL/pgSQL function inline_code_block line 27 at SQL statement
postgis postgresql function
New contributor
add a comment |
I`d like to create a Hex Grid using Hex grid algorithm for PostGIS (https://medium.com/@goldrydigital/hex-grid-algorithm-for-postgis-4ac45f61d093)
DO $$
DECLARE
_curs CURSOR FOR SELECT geom3857 FROM nrw;
_table TEXT := 'nrw_hx_10k';
_srid INTEGER := 3857;
_height NUMERIC := 10000;
_width NUMERIC := _height * 0.866;
_geom GEOMETRY;
_hx GEOMETRY := ST_GeomFromText(
FORMAT('POLYGON((0 0, %s %s, %s %s, %s %s, %s %s, %s %s, 0 0))',
(_width * 0.5), (_height * 0.25),
(_width * 0.5), (_height * 0.75),
0 , _height,
(_width * -0.5), (_height * 0.75),
(_width * -0.5), (_height * 0.25)
), _srid);
BEGIN
CREATE TEMP TABLE hx_tmp (geom GEOMETRY(POLYGON));
OPEN _curs;
LOOP
FETCH
_curs INTO _geom;
EXIT WHEN NOT FOUND;
INSERT INTO hx_tmp
SELECT
ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON) geom
FROM
generate_series(
(st_xmin(_geom) / _width)::INTEGER * _width - _width,
(st_xmax(_geom) / _width)::INTEGER * _width + _width,
_width) x_series,
generate_series(
(st_ymin(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) - _height,
(st_ymax(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) + _height,
_height * 1.5) y_series
WHERE
ST_Intersects(ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON), _geom);
INSERT INTO hx_tmp
SELECT ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON) geom
FROM
generate_series(
(st_xmin(_geom) / _width)::INTEGER * _width - (_width * 1.5),
(st_xmax(_geom) / _width)::INTEGER * _width + _width,
_width) x_series,
generate_series(
(st_ymin(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) - (_height * 1.75),
(st_ymax(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) + _height,
_height * 1.5) y_series
WHERE
ST_Intersects(ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON), _geom);
END LOOP;
CLOSE _curs;
CREATE INDEX sidx_hx_tmp_geom ON hx_tmp USING GIST (geom);
EXECUTE 'DROP TABLE IF EXISTS '|| _table;
EXECUTE 'CREATE TABLE '|| _table ||' (geom GEOMETRY(POLYGON, '|| _srid ||'))';
EXECUTE 'INSERT INTO '|| _table ||' SELECT * FROM hx_tmp GROUP BY geom';
EXECUTE 'CREATE INDEX sidx_'|| _table ||'_geom ON '|| _table ||' USING GIST (geom)';
DROP TABLE IF EXISTS hx_tmp;
END $$;
When I try to use the function, I get the following error:
ERROR: function generate_series(numeric, numeric, numeric) does not exist
LINE 5: generate_series(
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: INSERT INTO hx_tmp
SELECT
ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON) geom
FROM
generate_series(
(st_xmin(_geom) / _width)::INTEGER * _width - _width,
(st_xmax(_geom) / _width)::INTEGER * _width + _width,
_width) x_series,
generate_series(
(st_ymin(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) - _height,
(st_ymax(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) + _height,
_height * 1.5) y_series
WHERE
ST_Intersects(ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON), _geom)
CONTEXT: PL/pgSQL function inline_code_block line 27 at SQL statement
********** Error **********
ERROR: function generate_series(numeric, numeric, numeric) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Context: PL/pgSQL function inline_code_block line 27 at SQL statement
postgis postgresql function
New contributor
I`d like to create a Hex Grid using Hex grid algorithm for PostGIS (https://medium.com/@goldrydigital/hex-grid-algorithm-for-postgis-4ac45f61d093)
DO $$
DECLARE
_curs CURSOR FOR SELECT geom3857 FROM nrw;
_table TEXT := 'nrw_hx_10k';
_srid INTEGER := 3857;
_height NUMERIC := 10000;
_width NUMERIC := _height * 0.866;
_geom GEOMETRY;
_hx GEOMETRY := ST_GeomFromText(
FORMAT('POLYGON((0 0, %s %s, %s %s, %s %s, %s %s, %s %s, 0 0))',
(_width * 0.5), (_height * 0.25),
(_width * 0.5), (_height * 0.75),
0 , _height,
(_width * -0.5), (_height * 0.75),
(_width * -0.5), (_height * 0.25)
), _srid);
BEGIN
CREATE TEMP TABLE hx_tmp (geom GEOMETRY(POLYGON));
OPEN _curs;
LOOP
FETCH
_curs INTO _geom;
EXIT WHEN NOT FOUND;
INSERT INTO hx_tmp
SELECT
ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON) geom
FROM
generate_series(
(st_xmin(_geom) / _width)::INTEGER * _width - _width,
(st_xmax(_geom) / _width)::INTEGER * _width + _width,
_width) x_series,
generate_series(
(st_ymin(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) - _height,
(st_ymax(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) + _height,
_height * 1.5) y_series
WHERE
ST_Intersects(ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON), _geom);
INSERT INTO hx_tmp
SELECT ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON) geom
FROM
generate_series(
(st_xmin(_geom) / _width)::INTEGER * _width - (_width * 1.5),
(st_xmax(_geom) / _width)::INTEGER * _width + _width,
_width) x_series,
generate_series(
(st_ymin(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) - (_height * 1.75),
(st_ymax(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) + _height,
_height * 1.5) y_series
WHERE
ST_Intersects(ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON), _geom);
END LOOP;
CLOSE _curs;
CREATE INDEX sidx_hx_tmp_geom ON hx_tmp USING GIST (geom);
EXECUTE 'DROP TABLE IF EXISTS '|| _table;
EXECUTE 'CREATE TABLE '|| _table ||' (geom GEOMETRY(POLYGON, '|| _srid ||'))';
EXECUTE 'INSERT INTO '|| _table ||' SELECT * FROM hx_tmp GROUP BY geom';
EXECUTE 'CREATE INDEX sidx_'|| _table ||'_geom ON '|| _table ||' USING GIST (geom)';
DROP TABLE IF EXISTS hx_tmp;
END $$;
When I try to use the function, I get the following error:
ERROR: function generate_series(numeric, numeric, numeric) does not exist
LINE 5: generate_series(
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: INSERT INTO hx_tmp
SELECT
ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON) geom
FROM
generate_series(
(st_xmin(_geom) / _width)::INTEGER * _width - _width,
(st_xmax(_geom) / _width)::INTEGER * _width + _width,
_width) x_series,
generate_series(
(st_ymin(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) - _height,
(st_ymax(_geom) / (_height * 1.5))::INTEGER * (_height * 1.5) + _height,
_height * 1.5) y_series
WHERE
ST_Intersects(ST_Translate(_hx, x_series, y_series)::GEOMETRY(POLYGON), _geom)
CONTEXT: PL/pgSQL function inline_code_block line 27 at SQL statement
********** Error **********
ERROR: function generate_series(numeric, numeric, numeric) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Context: PL/pgSQL function inline_code_block line 27 at SQL statement
postgis postgresql function
postgis postgresql function
New contributor
New contributor
New contributor
asked 8 mins ago
dlama49 dlama49
1
1
New contributor
New contributor
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "79"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
dlama49 is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f318433%2ferror-with-generate-series%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
dlama49 is a new contributor. Be nice, and check out our Code of Conduct.
dlama49 is a new contributor. Be nice, and check out our Code of Conduct.
dlama49 is a new contributor. Be nice, and check out our Code of Conduct.
dlama49 is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Geographic Information Systems Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f318433%2ferror-with-generate-series%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown