Timeline for Create table from results of ST_Intersection queries in PostGIS
Current License: CC BY-SA 3.0
25 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Dec 4, 2016 at 1:11 | answer | added | TurboGraphxBeige | timeline score: 0 | |
| Dec 1, 2016 at 15:37 | comment | added | Inactivated Account | @AliciaRijo you should be able to use the 'create table newTable as (sql goes here)' but good you got it to work with INTO... if you could post your final SQL with the SQL spatial function, that would be valuable. thanks! | |
| Nov 30, 2016 at 20:07 | comment | added | Alicia Rijo | Hello! I finally find the solution, I just needed to add INTO, something like this: SELECT rw.gid, rw.name, rw.type, rw.geom INTO RAILS FROM railways rw WHERE "type" = 'rail'; | |
| Nov 30, 2016 at 17:10 | comment | added | Inactivated Account | Alicia: as stated, you are missing the rw alias on the rail table, so where you say 'from waterways w, rail' it should be 'from waterways as w, rail as rw' (I always us as when aliasing a table) - try that out and let us know if it works. Want to get the SQL syntax correct for using the spatial functions combined with the rest of the query... Thanks!!! | |
| Nov 30, 2016 at 16:48 | comment | added | John Powell | @DPSSpatial, don't get me started :-) | |
| Nov 30, 2016 at 16:42 | comment | added | Inactivated Account | @JohnBarça ArcGIS for Everything© | |
| Nov 30, 2016 at 16:36 | comment | added | John Powell | @DPSSpatial, ouch, though I see your point. | |
| Nov 30, 2016 at 15:00 | history | reopened |
Inactivated Account Andre Silva John Powell Mapperz♦ |
||
| Nov 30, 2016 at 14:01 | comment | added | Inactivated Account | @JohnBarça exactly... It drives me nuts... if it were up to me all ArcGIS questions on this forum would be closed as they belong on GeoNet. | |
| Nov 30, 2016 at 12:16 | comment | added | Vince |
The error here continues to be a failure of SQL fundamentals -- Inability to recognize the "you left out the rw reference in the query" error from the database
|
|
| Nov 30, 2016 at 11:06 | history | edited | PolyGeo♦ | CC BY-SA 3.0 |
Removed commentary
|
| Nov 30, 2016 at 10:20 | comment | added | John Powell | @DPSSpatial. What is your point about 2/3 of people being ArcGIS users. I am very much in the open source camp and would also not have closed this question, as I think it is not just a SQL question. Are you suggesting that the review queues would be better handled by people with that area of expertise. I almost always skip reviews where the question is ArcGIS related (unless it is just obviously a totally lazy give me the codez one line question) for exactly this reason. | |
| Nov 30, 2016 at 9:48 | review | Reopen votes | |||
| Nov 30, 2016 at 15:00 | |||||
| Nov 30, 2016 at 9:32 | history | edited | Alicia Rijo | CC BY-SA 3.0 |
added 712 characters in body
|
| Nov 30, 2016 at 2:47 | comment | added | Vince |
No, not ON ST_Intersection(...), but ON ST_Intersects(...). The latter returns a Boolean, while the former returns a geometry (or geography, depending on input). Calculating the intersection is computationally expensive, so it should only be done when necessary.
|
|
| Nov 30, 2016 at 0:53 | comment | added | MickyT |
Since this is on hold, you will also need to do some work on your second query once the RAIL table is fixed as per @Micha's answer. There is a couple of ways to do this query, here's an example of one SELECT rw.gid, rw.name, rw.type, w.name, w.type, case when r.geom is null then 0 else 1 end AS intersects_river FROM rail rw LEFT OUTER JOIN rivers r ON ST_INTERSECTION(rw.geom, r.geom)
|
|
| Nov 29, 2016 at 22:45 | review | Reopen votes | |||
| Nov 29, 2016 at 23:20 | |||||
| Nov 29, 2016 at 22:29 | comment | added | Inactivated Account | Once again 2/3 people who voted to close this question are ArcGIS users. | |
| Nov 29, 2016 at 22:29 | history | edited | Inactivated Account | CC BY-SA 3.0 |
added more details to include the use of multiple queries and altered the title to include the use of a spatial function.
|
| Nov 29, 2016 at 22:21 | history | closed |
Vince alphabetasoup Fezter |
Not suitable for this site | |
| Nov 29, 2016 at 21:53 | history | edited | whyzar | CC BY-SA 3.0 |
corrected spelling, fixed grammar, improved formatting
|
| Nov 29, 2016 at 21:39 | review | Close votes | |||
| Nov 29, 2016 at 22:21 | |||||
| Nov 29, 2016 at 21:26 | answer | added | Micha | timeline score: 1 | |
| Nov 29, 2016 at 21:23 | comment | added | Vince |
This is really a fundamental SQL issue, and not anything GIS specific. If you look at the documentation, you'll find plenty of examples. I'd recommend an explicit JOIN rather than an implicit one in the FROM list.
|
|
| Nov 29, 2016 at 21:16 | history | asked | Alicia Rijo | CC BY-SA 3.0 |