PostGIS ST_Within ST_intersects differing SRIDsHow to route between lat/lon pairs in pgRouting?PostGIS:...
CREATE opcode: what does it really do?
Would a virus be able to change eye and hair colour?
Is expanding the research of a group into machine learning as a PhD student risky?
What is paid subscription needed for in Mortal Kombat 11?
Detecting if an element is found inside a container
Two monoidal structures and copowering
How do we know the LHC results are robust?
How does the UK government determine the size of a mandate?
Avoiding estate tax by giving multiple gifts
Was Spock the First Vulcan in Starfleet?
What happens if you roll doubles 3 times then land on "Go to jail?"
What is the best translation for "slot" in the context of multiplayer video games?
Why are there no referendums in the US?
How did Doctor Strange see the winning outcome in Avengers: Infinity War?
Method to test if a number is a perfect power?
Did Dumbledore lie to Harry about how long he had James Potter's invisibility cloak when he was examining it? If so, why?
How do I find the solutions of the following equation?
I'm in charge of equipment buying but no one's ever happy with what I choose. How to fix this?
Proof of work - lottery approach
What can we do to stop prior company from asking us questions?
How can a function with a hole (removable discontinuity) equal a function with no hole?
Is there a good way to store credentials outside of a password manager?
How do I rename a Linux host without needing to reboot for the rename to take effect?
How can I get through very long and very dry, but also very useful technical documents when learning a new tool?
PostGIS ST_Within ST_intersects differing SRIDs
How to route between lat/lon pairs in pgRouting?PostGIS: ST_Transform function conversion problemPostgis | Problems Reprojecting a Point from UTM ED50 to GEOGRAPHIC ETRS89PostGis: Geometry from UTM textHelp using ST_Buffer for pointsAdd SRID to Pointtransform longitude and latitude to postgis geometry failed: exceeded limits(-14)ST_Area and SRID 4326'UPDATE 0' results for ST_Within query - locating points within polygonsArcGIS vs QGIS vs PostGIS projection issue
I have a layer of points and want to join some data from polygons where the two coincide. I might have several points in one poly.
The points were generated from a CSV as;
UPDATE t_osab_with_voa_ndr SET geom_point_4258 = ST_SetSrid(ST_MakePoint(longitude, latitude),4258);
Where they are stated as being ETRS89 projection which is SRID 4258
I'm running into problems as the points have SRID 4258 and the Polys are 27700 I've tried using ;
ALTER TABLE t_osab_with_voa_ndr
ALTER COLUMN geom_point_4258 TYPE geometry(point,27700)
USING ST_Transform(geom_point_4258,27700);
When I look in QGIS I see this;
But when I run;
create or replace view v_spatialjoin_northeast_st_within1 as
select * from t_osab_with_voa_ndr , d_geom_northeast where st_within(t_osab_with_voa_ndr.geom_point, d_geom_northeast.geom);
I get no results when I can see I should
Question 1.
Can i combine the ST_Within / ST_Interects and specify the SRID in the process or am i better transforming the points first?
EDIT:
Reason for my blanks was SQL problem with ST_Within, but still interested to know if you can set SRID or what is the fastest way to execute this
postgis postgresql
edited Nov 24 '18 at 17:21
Vince
14.8k32749
asked Apr 26 '18 at 10:04
mapping dommapping dom
698513
|
show 5 more comments
I have a layer of points and want to join some data from polygons where the two coincide. I might have several points in one poly.
The points were generated from a CSV as;
UPDATE t_osab_with_voa_ndr SET geom_point_4258 = ST_SetSrid(ST_MakePoint(longitude, latitude),4258);
Where they are stated as being ETRS89 projection which is SRID 4258
I'm running into problems as the points have SRID 4258 and the Polys are 27700 I've tried using ;
ALTER TABLE t_osab_with_voa_ndr
ALTER COLUMN geom_point_4258 TYPE geometry(point,27700)
USING ST_Transform(geom_point_4258,27700);
When I look in QGIS I see this;
But when I run;
create or replace view v_spatialjoin_northeast_st_within1 as
select * from t_osab_with_voa_ndr , d_geom_northeast where st_within(t_osab_with_voa_ndr.geom_point, d_geom_northeast.geom);
I get no results when I can see I should
Question 1.
Can i combine the ST_Within / ST_Interects and specify the SRID in the process or am i better transforming the points first?
EDIT:
Reason for my blanks was SQL problem with ST_Within, but still interested to know if you can set SRID or what is the fastest way to execute this
postgis postgresql
edited Nov 24 '18 at 17:21
Vince
14.8k32749
asked Apr 26 '18 at 10:04
mapping dommapping dom
698513
shouldn't you transform geom_point_4258?
– Ian Turton♦
Apr 26 '18 at 10:16
@IanTurton Apologies i'd been chopping and changing in postgres to look for a fix, yes i should and have. Same error
– mapping dom
Apr 26 '18 at 10:20
@ThingumaBob good spot but still get no results
– mapping dom
Apr 26 '18 at 10:30
You can use ST_SetSRID/ST_Transform within the query, however, if the query uses a spatial index, this will likely prevent it from being used, as it becomes non SARGABLE. If you have to do a full table scan on the points anyway, then there is no harm, but you would certainly want an index on the polygon table.
– John Powell
Apr 26 '18 at 10:48
@JohnPowellakaBarça That sounds like the answer to the originally posed question, can you add it. Although i know realise my error was from elsewhere
– mapping dom
Apr 26 '18 at 10:50
|
show 5 more comments
I have a layer of points and want to join some data from polygons where the two coincide. I might have several points in one poly.
The points were generated from a CSV as;
UPDATE t_osab_with_voa_ndr SET geom_point_4258 = ST_SetSrid(ST_MakePoint(longitude, latitude),4258);
Where they are stated as being ETRS89 projection which is SRID 4258
I'm running into problems as the points have SRID 4258 and the Polys are 27700 I've tried using ;
ALTER TABLE t_osab_with_voa_ndr
ALTER COLUMN geom_point_4258 TYPE geometry(point,27700)
USING ST_Transform(geom_point_4258,27700);
When I look in QGIS I see this;
But when I run;
create or replace view v_spatialjoin_northeast_st_within1 as
select * from t_osab_with_voa_ndr , d_geom_northeast where st_within(t_osab_with_voa_ndr.geom_point, d_geom_northeast.geom);
I get no results when I can see I should
Question 1.
Can i combine the ST_Within / ST_Interects and specify the SRID in the process or am i better transforming the points first?
EDIT:
Reason for my blanks was SQL problem with ST_Within, but still interested to know if you can set SRID or what is the fastest way to execute this
postgis postgresql
edited Nov 24 '18 at 17:21
Vince
14.8k32749
asked Apr 26 '18 at 10:04
mapping dommapping dom
698513
I have a layer of points and want to join some data from polygons where the two coincide. I might have several points in one poly.
The points were generated from a CSV as;
UPDATE t_osab_with_voa_ndr SET geom_point_4258 = ST_SetSrid(ST_MakePoint(longitude, latitude),4258);
Where they are stated as being ETRS89 projection which is SRID 4258
I'm running into problems as the points have SRID 4258 and the Polys are 27700 I've tried using ;
ALTER TABLE t_osab_with_voa_ndr
ALTER COLUMN geom_point_4258 TYPE geometry(point,27700)
USING ST_Transform(geom_point_4258,27700);
When I look in QGIS I see this;
But when I run;
create or replace view v_spatialjoin_northeast_st_within1 as
select * from t_osab_with_voa_ndr , d_geom_northeast where st_within(t_osab_with_voa_ndr.geom_point, d_geom_northeast.geom);
I get no results when I can see I should
Question 1.
Can i combine the ST_Within / ST_Interects and specify the SRID in the process or am i better transforming the points first?
EDIT:
Reason for my blanks was SQL problem with ST_Within, but still interested to know if you can set SRID or what is the fastest way to execute this
postgis postgresql
postgis postgresql
edited Nov 24 '18 at 17:21
Vince
14.8k32749
asked Apr 26 '18 at 10:04
mapping dommapping dom
698513
edited Nov 24 '18 at 17:21
Vince
14.8k32749
asked Apr 26 '18 at 10:04
mapping dommapping dom
698513
edited Nov 24 '18 at 17:21
Vince
14.8k32749
edited Nov 24 '18 at 17:21
Vince
14.8k32749
edited Nov 24 '18 at 17:21
Vince
14.8k32749
14.8k32749
asked Apr 26 '18 at 10:04
mapping dommapping dom
698513
asked Apr 26 '18 at 10:04
mapping dommapping dom
698513
asked Apr 26 '18 at 10:04
mapping dommapping dom
698513
698513
shouldn't you transform geom_point_4258?
– Ian Turton♦
Apr 26 '18 at 10:16
@IanTurton Apologies i'd been chopping and changing in postgres to look for a fix, yes i should and have. Same error
– mapping dom
Apr 26 '18 at 10:20
@ThingumaBob good spot but still get no results
– mapping dom
Apr 26 '18 at 10:30
You can use ST_SetSRID/ST_Transform within the query, however, if the query uses a spatial index, this will likely prevent it from being used, as it becomes non SARGABLE. If you have to do a full table scan on the points anyway, then there is no harm, but you would certainly want an index on the polygon table.
– John Powell
Apr 26 '18 at 10:48
@JohnPowellakaBarça That sounds like the answer to the originally posed question, can you add it. Although i know realise my error was from elsewhere
– mapping dom
Apr 26 '18 at 10:50
|
show 5 more comments
shouldn't you transform geom_point_4258?
– Ian Turton♦
Apr 26 '18 at 10:16
@IanTurton Apologies i'd been chopping and changing in postgres to look for a fix, yes i should and have. Same error
– mapping dom
Apr 26 '18 at 10:20
@ThingumaBob good spot but still get no results
– mapping dom
Apr 26 '18 at 10:30
You can use ST_SetSRID/ST_Transform within the query, however, if the query uses a spatial index, this will likely prevent it from being used, as it becomes non SARGABLE. If you have to do a full table scan on the points anyway, then there is no harm, but you would certainly want an index on the polygon table.
– John Powell
Apr 26 '18 at 10:48
@JohnPowellakaBarça That sounds like the answer to the originally posed question, can you add it. Although i know realise my error was from elsewhere
– mapping dom
Apr 26 '18 at 10:50
shouldn't you transform geom_point_4258?
– Ian Turton♦
Apr 26 '18 at 10:16
shouldn't you transform geom_point_4258?
– Ian Turton♦
Apr 26 '18 at 10:16
@IanTurton Apologies i'd been chopping and changing in postgres to look for a fix, yes i should and have. Same error
– mapping dom
Apr 26 '18 at 10:20
@IanTurton Apologies i'd been chopping and changing in postgres to look for a fix, yes i should and have. Same error
– mapping dom
Apr 26 '18 at 10:20
@ThingumaBob good spot but still get no results
– mapping dom
Apr 26 '18 at 10:30
@ThingumaBob good spot but still get no results
– mapping dom
Apr 26 '18 at 10:30
You can use ST_SetSRID/ST_Transform within the query, however, if the query uses a spatial index, this will likely prevent it from being used, as it becomes non SARGABLE. If you have to do a full table scan on the points anyway, then there is no harm, but you would certainly want an index on the polygon table.
– John Powell
Apr 26 '18 at 10:48
You can use ST_SetSRID/ST_Transform within the query, however, if the query uses a spatial index, this will likely prevent it from being used, as it becomes non SARGABLE. If you have to do a full table scan on the points anyway, then there is no harm, but you would certainly want an index on the polygon table.
– John Powell
Apr 26 '18 at 10:48
@JohnPowellakaBarça That sounds like the answer to the originally posed question, can you add it. Although i know realise my error was from elsewhere
– mapping dom
Apr 26 '18 at 10:50
@JohnPowellakaBarça That sounds like the answer to the originally posed question, can you add it. Although i know realise my error was from elsewhere
– mapping dom
Apr 26 '18 at 10:50
|
show 5 more comments
2 Answers
2
active
oldest
votes
The reason for the blanks in this instance was due to my query, what worked was rewriting this as below, code taken from good blog post here
SELECT pts.*, blocks.inspireid as inspire_poly_id
FROM t_osab_with_voa_ndr AS pts
INNER JOIN d_geom_northeast AS blocks
ON st_within(pts.geom_point, blocks.geom);
answered Apr 26 '18 at 10:52
mapping dommapping dom
698513
...actually, that's weird since, although the(INNER) JOIN ... ON ...should be faster here than (and the preferred style in general in favour to) aCROSS JOIN ... WHERE ..., in the end it should give the same reuslts...
– ThingumaBob
Apr 26 '18 at 11:12
I'm running the same process in QGIS to double check my code..
– mapping dom
Apr 26 '18 at 12:14
add a comment |
For me what works this, to use the ST_transforn instead of ST_Set SRID in order to chance the SPATIAL RWFEREBCE SYSTEM, after that the SPATIAL query wtih INNER JOIN or the other one works perfectly.
Here a definition:
https://postgis.net/2013/08/30/tip_ST_Set_or_Transform/
answered 3 mins ago
Marcelo zentenoMarcelo zenteno
1
New contributor
Marcelo zenteno is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
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
});
}
});
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%2f280905%2fpostgis-st-within-st-intersects-differing-srids%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
The reason for the blanks in this instance was due to my query, what worked was rewriting this as below, code taken from good blog post here
SELECT pts.*, blocks.inspireid as inspire_poly_id
FROM t_osab_with_voa_ndr AS pts
INNER JOIN d_geom_northeast AS blocks
ON st_within(pts.geom_point, blocks.geom);
answered Apr 26 '18 at 10:52
mapping dommapping dom
698513
...actually, that's weird since, although the(INNER) JOIN ... ON ...should be faster here than (and the preferred style in general in favour to) aCROSS JOIN ... WHERE ..., in the end it should give the same reuslts...
– ThingumaBob
Apr 26 '18 at 11:12
I'm running the same process in QGIS to double check my code..
– mapping dom
Apr 26 '18 at 12:14
add a comment |
The reason for the blanks in this instance was due to my query, what worked was rewriting this as below, code taken from good blog post here
SELECT pts.*, blocks.inspireid as inspire_poly_id
FROM t_osab_with_voa_ndr AS pts
INNER JOIN d_geom_northeast AS blocks
ON st_within(pts.geom_point, blocks.geom);
answered Apr 26 '18 at 10:52
mapping dommapping dom
698513
...actually, that's weird since, although the(INNER) JOIN ... ON ...should be faster here than (and the preferred style in general in favour to) aCROSS JOIN ... WHERE ..., in the end it should give the same reuslts...
– ThingumaBob
Apr 26 '18 at 11:12
I'm running the same process in QGIS to double check my code..
– mapping dom
Apr 26 '18 at 12:14
add a comment |
The reason for the blanks in this instance was due to my query, what worked was rewriting this as below, code taken from good blog post here
SELECT pts.*, blocks.inspireid as inspire_poly_id
FROM t_osab_with_voa_ndr AS pts
INNER JOIN d_geom_northeast AS blocks
ON st_within(pts.geom_point, blocks.geom);
answered Apr 26 '18 at 10:52
mapping dommapping dom
698513
The reason for the blanks in this instance was due to my query, what worked was rewriting this as below, code taken from good blog post here
SELECT pts.*, blocks.inspireid as inspire_poly_id
FROM t_osab_with_voa_ndr AS pts
INNER JOIN d_geom_northeast AS blocks
ON st_within(pts.geom_point, blocks.geom);
answered Apr 26 '18 at 10:52
mapping dommapping dom
698513
answered Apr 26 '18 at 10:52
mapping dommapping dom
698513
answered Apr 26 '18 at 10:52
mapping dommapping dom
698513
answered Apr 26 '18 at 10:52
mapping dommapping dom
698513
698513
...actually, that's weird since, although the(INNER) JOIN ... ON ...should be faster here than (and the preferred style in general in favour to) aCROSS JOIN ... WHERE ..., in the end it should give the same reuslts...
– ThingumaBob
Apr 26 '18 at 11:12
I'm running the same process in QGIS to double check my code..
– mapping dom
Apr 26 '18 at 12:14
add a comment |
...actually, that's weird since, although the(INNER) JOIN ... ON ...should be faster here than (and the preferred style in general in favour to) aCROSS JOIN ... WHERE ..., in the end it should give the same reuslts...
– ThingumaBob
Apr 26 '18 at 11:12
I'm running the same process in QGIS to double check my code..
– mapping dom
Apr 26 '18 at 12:14
...actually, that's weird since, although the
(INNER) JOIN ... ON ... should be faster here than (and the preferred style in general in favour to) a CROSS JOIN ... WHERE ..., in the end it should give the same reuslts...– ThingumaBob
Apr 26 '18 at 11:12
...actually, that's weird since, although the
(INNER) JOIN ... ON ... should be faster here than (and the preferred style in general in favour to) a CROSS JOIN ... WHERE ..., in the end it should give the same reuslts...– ThingumaBob
Apr 26 '18 at 11:12
I'm running the same process in QGIS to double check my code..
– mapping dom
Apr 26 '18 at 12:14
I'm running the same process in QGIS to double check my code..
– mapping dom
Apr 26 '18 at 12:14
add a comment |
For me what works this, to use the ST_transforn instead of ST_Set SRID in order to chance the SPATIAL RWFEREBCE SYSTEM, after that the SPATIAL query wtih INNER JOIN or the other one works perfectly.
Here a definition:
https://postgis.net/2013/08/30/tip_ST_Set_or_Transform/
answered 3 mins ago
Marcelo zentenoMarcelo zenteno
1
New contributor
Marcelo zenteno is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
For me what works this, to use the ST_transforn instead of ST_Set SRID in order to chance the SPATIAL RWFEREBCE SYSTEM, after that the SPATIAL query wtih INNER JOIN or the other one works perfectly.
Here a definition:
https://postgis.net/2013/08/30/tip_ST_Set_or_Transform/
answered 3 mins ago
Marcelo zentenoMarcelo zenteno
1
New contributor
Marcelo zenteno is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
For me what works this, to use the ST_transforn instead of ST_Set SRID in order to chance the SPATIAL RWFEREBCE SYSTEM, after that the SPATIAL query wtih INNER JOIN or the other one works perfectly.
Here a definition:
https://postgis.net/2013/08/30/tip_ST_Set_or_Transform/
answered 3 mins ago
Marcelo zentenoMarcelo zenteno
1
New contributor
Marcelo zenteno is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
For me what works this, to use the ST_transforn instead of ST_Set SRID in order to chance the SPATIAL RWFEREBCE SYSTEM, after that the SPATIAL query wtih INNER JOIN or the other one works perfectly.
Here a definition:
https://postgis.net/2013/08/30/tip_ST_Set_or_Transform/
answered 3 mins ago
Marcelo zentenoMarcelo zenteno
1
New contributor
Marcelo zenteno is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 3 mins ago
Marcelo zentenoMarcelo zenteno
1
New contributor
Marcelo zenteno is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 3 mins ago
Marcelo zentenoMarcelo zenteno
1
answered 3 mins ago
Marcelo zentenoMarcelo zenteno
1
1
New contributor
Marcelo zenteno is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Marcelo zenteno is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Marcelo zenteno is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
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%2f280905%2fpostgis-st-within-st-intersects-differing-srids%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
shouldn't you transform geom_point_4258?
– Ian Turton♦
Apr 26 '18 at 10:16
@IanTurton Apologies i'd been chopping and changing in postgres to look for a fix, yes i should and have. Same error
– mapping dom
Apr 26 '18 at 10:20
@ThingumaBob good spot but still get no results
– mapping dom
Apr 26 '18 at 10:30
You can use ST_SetSRID/ST_Transform within the query, however, if the query uses a spatial index, this will likely prevent it from being used, as it becomes non SARGABLE. If you have to do a full table scan on the points anyway, then there is no harm, but you would certainly want an index on the polygon table.
– John Powell
Apr 26 '18 at 10:48
@JohnPowellakaBarça That sounds like the answer to the originally posed question, can you add it. Although i know realise my error was from elsewhere
– mapping dom
Apr 26 '18 at 10:50