AWS Redshift ST_Intersection incorrect response

Question

When using st_intersection, the response is incorrect, it returns the second geometry even though first one intersects with second one.
select 
ST_geomfromwkb('01030000000100000007000000C1029832703C3440D4B837BF61FA484037FFAF3A723C34401F86562767FA4840F4C473B6803C3440A298BC0166FA4840B2D5E594803C34405072874D64FA484038842A357B3C34405359147651FA484046B1DCD26A3C34402992AF0452FA4840C1029832703C3440D4B837BF61FA4840') geom1,
st_geomfromwkb('01030000000100000070000000688364BEE03B344050A9B540F3F9484094A964BDE03B34400804DB40F3F94840BAF977BDE03B3440F9303541F3F948403DB4BD20E93B344073EE57FB02FA4840994AC320E93B3440091562FB02FA4840909D01A2EE3B344092FC55AE0CFA4840F10E11A2EE3B3440E26472AE0CFA484074EA67CFF33B3440A57C77A416FA484074DB79CFF33B3440D1D196A416FA4840CAB1D22AFA3B34403530FDCC20FA48404B1DD52AFA3B3440681301CD20FA4840B47D8992013C344065A8A6C22CFA48407857BD92013C3440601707C32CFA48402722D20B053C344099F9F05C34FA48408E33DE0B053C3440D3CA0C5D34FA484033454B89073C344039DEAD6C3AFA484017E56D89073C3440BF21196D3AFA4840697452B7083C3440DF8224703FFA4840632962B7083C3440564F8C703FFA484029C8400B093C34402B7DD14944FA484083E4420B093C34403E59E64944FA48403C12DE6F093C34406EEC0F3447FA4840D0670770093C3440F10D6B3447FA48400FE80D71093C3440A9CE8F3447FA4840CB31EB1C183C3440CE6235B648FA4840179A2F1D183C344097183AB648FA48401F26901E183C34403DFB55B648FA484003C6181F183C34405D3562B648FA48406C0E6ECB263C3440928C6A8F49FA48404E049BCF263C344043CDC98F49FA48401D427FD3263C344026B1669049FA4840B6C5F8D6263C344025DD3B9149FA4840A431E9D9263C34405E0A429249FA484080D636DC263C3440A445709349FA48406893CDDD263C3440C93DBC9449FA4840DD859FDE263C3440B39D1A9649FA48408589692C283C3440A56943DB4DFA48407859732C283C344069E966DB4DFA4840D09CD8CF293C34401693C27954FA48401794E0CF293C34408E23E77954FA48400DC711562A3C34404882CC4257FA484014B619562A3C3440585BED4257FA4840C87BE6052D3C3440808FBC5E60FA4840552DF9052D3C344081620E5F60FA48406E4897012E3C344040B4B26E66FA48404342A2012E3C34406FFAE06E66FA4840685CCFF82F3C3440C3E70AB96CFA4840A3CD01F92F3C3440FEC15CB96CFA4840222B02FA2F3C3440BE0750B96CFA48408DFB126C403C3440166576616BFA4840F31C72C24B3C3440133B336E6AFA484034477AC24B3C3440628C326E6AFA4840F2A5688E573C34403355897269FA4840861B55C3623C3440BDD7437F68FA4840D4206F3A723C3440A0285C2767FA48400B3DF13A723C3440945B512767FA4840DD6324B6803C3440C8E1C20166FA484085F7C1B6803C344007EEB40166FA48400AE1E60D843C3440C97CDEAD65FA48405790470E843C34400F4BD5AD65FA4840EBFC327FA03C344064DAC70E63FA48408F0F9B7FA03C34400596BE0E63FA484063EE81E2A83C3440E0BE3E5662FA48403D5244E3A83C344035272F5662FA4840C986F26FB33C34401128E48C61FA484050561670B33C3440B269E18C61FA4840C588D182BA3C34401A885EFE60FA48408BB4D783BA3C3440B7B740FE60FA484011D1E083BA3C34405E50E7FD60FA4840E06B911BB73C34402849E1AC50FA4840F8037D1BB73C3440316BA0AC50FA48409A548AD8B63C3440B10AA00D50FA48409D2961D8B63C3440349C1C0D50FA48408934ECC3B33C3440B4D6411C41FA4840C636E4C3B33C34409625121C41FA48405C7607ABB13C3440E03264DB30FA4840AFCC46D5AF3C344089B98F7824FA4840C40243D5AF3C34408DE37A7824FA4840D2834C79AD3C3440006CC38619FA484098BF4779AD3C3440091EB08619FA4840997A6D9BA93C3440CD64EEA10BFA4840FB840733A63C3440409BC03A00FA48404F2B0233A63C3440F22DB03A00FA4840A2E39E33A23C3440F0124BE4F4F94840F4CE9833A23C344057073BE4F4F94840DB17A9409F3C344052DD0D9EEDF9484075449A409F3C34401D90ED9DEDF948408C9417B3983C3440D952C8D6E0F948405D96D6B2983C34401ECF78D6E0F948401C5CDBB1983C34401BE387D6E0F9484033BABB108F3C3440F671EBB8E1F94840C77297108F3C3440C0BCEEB8E1F948402B133190853C344039820293E2F94840B951BD8F853C3440BD7C0C93E2F9484057253C2A723C3440DB800B2EE4F9484046459729723C344085C71A2EE4F94840C1534993523C34400A6A8B6CE7F9484014C3F177453C3440F49677C4E8F94840DD0BC177453C344019BB7CC4E8F94840331D717E383C34405280222DEAF94840A9D0787D383C3440468C3B2DEAF94840E88717D82B3C3440B2172A5BEBF948400CF5D2D72B3C344043C6305BEBF9484044F3E6EA1F3C344054D78391ECF94840261E1AEA1F3C344016619791ECF948402BF511CB133C3440192559A6EDF94840590260CA133C34409BF26AA6EDF9484015F5E7AF023C34408210EA8CEFF948406F57D3AF023C34407657EC8CEFF948404F66B8F9F13B3440860AAD62F1F94840869DA1F9F13B3440CB86AF62F1F94840688364BEE03B344050A9B540F3F94840') geom2,
ST_Intersection(ST_geomfromwkb('01030000000100000007000000C1029832703C3440D4B837BF61FA484037FFAF3A723C34401F86562767FA4840F4C473B6803C3440A298BC0166FA4840B2D5E594803C34405072874D64FA484038842A357B3C34405359147651FA484046B1DCD26A3C34402992AF0452FA4840C1029832703C3440D4B837BF61FA4840'),st_geomfromwkb('01030000000100000070000000688364BEE03B344050A9B540F3F9484094A964BDE03B34400804DB40F3F94840BAF977BDE03B3440F9303541F3F948403DB4BD20E93B344073EE57FB02FA4840994AC320E93B3440091562FB02FA4840909D01A2EE3B344092FC55AE0CFA4840F10E11A2EE3B3440E26472AE0CFA484074EA67CFF33B3440A57C77A416FA484074DB79CFF33B3440D1D196A416FA4840CAB1D22AFA3B34403530FDCC20FA48404B1DD52AFA3B3440681301CD20FA4840B47D8992013C344065A8A6C22CFA48407857BD92013C3440601707C32CFA48402722D20B053C344099F9F05C34FA48408E33DE0B053C3440D3CA0C5D34FA484033454B89073C344039DEAD6C3AFA484017E56D89073C3440BF21196D3AFA4840697452B7083C3440DF8224703FFA4840632962B7083C3440564F8C703FFA484029C8400B093C34402B7DD14944FA484083E4420B093C34403E59E64944FA48403C12DE6F093C34406EEC0F3447FA4840D0670770093C3440F10D6B3447FA48400FE80D71093C3440A9CE8F3447FA4840CB31EB1C183C3440CE6235B648FA4840179A2F1D183C344097183AB648FA48401F26901E183C34403DFB55B648FA484003C6181F183C34405D3562B648FA48406C0E6ECB263C3440928C6A8F49FA48404E049BCF263C344043CDC98F49FA48401D427FD3263C344026B1669049FA4840B6C5F8D6263C344025DD3B9149FA4840A431E9D9263C34405E0A429249FA484080D636DC263C3440A445709349FA48406893CDDD263C3440C93DBC9449FA4840DD859FDE263C3440B39D1A9649FA48408589692C283C3440A56943DB4DFA48407859732C283C344069E966DB4DFA4840D09CD8CF293C34401693C27954FA48401794E0CF293C34408E23E77954FA48400DC711562A3C34404882CC4257FA484014B619562A3C3440585BED4257FA4840C87BE6052D3C3440808FBC5E60FA4840552DF9052D3C344081620E5F60FA48406E4897012E3C344040B4B26E66FA48404342A2012E3C34406FFAE06E66FA4840685CCFF82F3C3440C3E70AB96CFA4840A3CD01F92F3C3440FEC15CB96CFA4840222B02FA2F3C3440BE0750B96CFA48408DFB126C403C3440166576616BFA4840F31C72C24B3C3440133B336E6AFA484034477AC24B3C3440628C326E6AFA4840F2A5688E573C34403355897269FA4840861B55C3623C3440BDD7437F68FA4840D4206F3A723C3440A0285C2767FA48400B3DF13A723C3440945B512767FA4840DD6324B6803C3440C8E1C20166FA484085F7C1B6803C344007EEB40166FA48400AE1E60D843C3440C97CDEAD65FA48405790470E843C34400F4BD5AD65FA4840EBFC327FA03C344064DAC70E63FA48408F0F9B7FA03C34400596BE0E63FA484063EE81E2A83C3440E0BE3E5662FA48403D5244E3A83C344035272F5662FA4840C986F26FB33C34401128E48C61FA484050561670B33C3440B269E18C61FA4840C588D182BA3C34401A885EFE60FA48408BB4D783BA3C3440B7B740FE60FA484011D1E083BA3C34405E50E7FD60FA4840E06B911BB73C34402849E1AC50FA4840F8037D1BB73C3440316BA0AC50FA48409A548AD8B63C3440B10AA00D50FA48409D2961D8B63C3440349C1C0D50FA48408934ECC3B33C3440B4D6411C41FA4840C636E4C3B33C34409625121C41FA48405C7607ABB13C3440E03264DB30FA4840AFCC46D5AF3C344089B98F7824FA4840C40243D5AF3C34408DE37A7824FA4840D2834C79AD3C3440006CC38619FA484098BF4779AD3C3440091EB08619FA4840997A6D9BA93C3440CD64EEA10BFA4840FB840733A63C3440409BC03A00FA48404F2B0233A63C3440F22DB03A00FA4840A2E39E33A23C3440F0124BE4F4F94840F4CE9833A23C344057073BE4F4F94840DB17A9409F3C344052DD0D9EEDF9484075449A409F3C34401D90ED9DEDF948408C9417B3983C3440D952C8D6E0F948405D96D6B2983C34401ECF78D6E0F948401C5CDBB1983C34401BE387D6E0F9484033BABB108F3C3440F671EBB8E1F94840C77297108F3C3440C0BCEEB8E1F948402B133190853C344039820293E2F94840B951BD8F853C3440BD7C0C93E2F9484057253C2A723C3440DB800B2EE4F9484046459729723C344085C71A2EE4F94840C1534993523C34400A6A8B6CE7F9484014C3F177453C3440F49677C4E8F94840DD0BC177453C344019BB7CC4E8F94840331D717E383C34405280222DEAF94840A9D0787D383C3440468C3B2DEAF94840E88717D82B3C3440B2172A5BEBF948400CF5D2D72B3C344043C6305BEBF9484044F3E6EA1F3C344054D78391ECF94840261E1AEA1F3C344016619791ECF948402BF511CB133C3440192559A6EDF94840590260CA133C34409BF26AA6EDF9484015F5E7AF023C34408210EA8CEFF948406F57D3AF023C34407657EC8CEFF948404F66B8F9F13B3440860AAD62F1F94840869DA1F9F13B3440CB86AF62F1F94840688364BEE03B344050A9B540F3F94840')) geom_intersection

re:Post Agent · Answer

The issue you're experiencing with ST_Intersection in Amazon Redshift appears to be related to how the function handles the geometries you've provided.

When using ST_Intersection, the function returns a geometry representing the intersection of two input geometries. If the geometries share space (intersect), it should return the shared portion. If they don't share any space or are empty, an empty geometry is returned.

In your case, if you're seeing the second geometry returned completely instead of just the intersection area, this could indicate one of several issues:

1. The first geometry might be completely contained within the second geometry, making the intersection equal to the first geometry.

2. There might be a precision issue with the coordinates that's affecting the calculation.

3. There could be an issue with the WKB (Well-Known Binary) representation of your geometries.

To verify if your geometries actually intersect, you can use the ST_Intersects function, which returns a boolean value indicating whether the geometries share any space:

```sql
SELECT ST_Intersects(
ST_geomfromwkb('01030000000100000007000000C1029832703C3440D4B837BF61FA484037FFAF3A723C34401F86562767FA4840F4C473B6803C3440A298BC0166FA4840B2D5E594803C34405072874D64FA484038842A357B3C34405359147651FA484046B1DCD26A3C34402992AF0452FA4840C1029832703C3440D4B837BF61FA4840'),
ST_geomfromwkb('01030000000100000070000000688364BEE03B344050A9B540F3F94840...')
);
```

If this returns true, the geometries do intersect, and the ST_Intersection function should return the shared portion, not the entire second geometry.

You might also want to try visualizing these geometries to better understand their spatial relationship, which could help identify why you're getting unexpected results from the ST_Intersection function.  
**Sources**  
[ST_Intersection - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/ST_Intersection-function.html)  
[ST_Intersects - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/ST_Intersects-function.html)

AWS Redshift ST_Intersection incorrect response

回答を追加する

関連するコンテンツ