0

I am working in a Django and have done a query to get a list of geometries from a table. I want to iterate through that list and insert them into my new query. My notional polygon is:

0103000020E6100000010000000A000000C3691AE97DAB434010D59670EBAB4C40A2D701742DE041403B9994F129824C401768318F8BEE41408A0A316D2EDF4B401F6D5C4CDF0E424032402134D5854B406264439762A4424073E3BF7E573F4B40CA3E65381A4E4340391B287128384B4098C3C80CD2234440585FB4D32E374C4098C3C80CD2234440585FB4D32E374C40FE49A2AD88F143406775822944974C40C3691AE97DAB434010D59670EBAB4C40

My latest attempt for a query is:

SELECT author, photo_id, user_id FROM point_database WHERE ST_Intersects(point_database.point_geom, ST_GeomFROMWKB('POLYGON(0103000020E6100000010000000A000000C3691AE97DAB434010D59670EBAB4C40A2D701742DE041403B9994F129824C401768318F8BEE41408A0A316D2EDF4B401F6D5C4CDF0E424032402134D5854B406264439762A4424073E3BF7E573F4B40CA3E65381A4E4340391B287128384B4098C3C80CD2234440585FB4D32E374C4098C3C80CD2234440585FB4D32E374C40FE49A2AD88F143406775822944974C40C3691AE97DAB434010D59670EBAB4C40)'))

This resulted in the less than helpful

 ERROR: Invalid endian flag value encountered.

SQL state: XX000

I know I could typically do something along the lines of ST_Intersects(point_database.geom, polygon_database.geom) but not sure how to structure a query with the ewkb hex especially when passing it through a secondary loop in a greater django framework.

2

I can create your geometry without ST_GeomFROMWKB using this:

create table geomtest as (

    select cast(
'0103000020E6100000010000000A000000C3691AE97DAB434010D59670EBAB4C40A2D701742DE041403B9994F129824C401768318F8BEE41408A0A316D2EDF4B401F6D5C4CDF0E424032402134D5854B406264439762A4424073E3BF7E573F4B40CA3E65381A4E4340391B287128384B4098C3C80CD2234440585FB4D32E374C4098C3C80CD2234440585FB4D32E374C40FE49A2AD88F143406775822944974C40C3691AE97DAB434010D59670EBAB4C40' 
as geometry) as geom
    )

And can see your geometry in QGIS.

So your query might look like:

SELECT author, photo_id, user_id 
FROM point_database
, (
select cast('0103000020E6100000010000000A000000C3691AE97DAB434010D59670EBAB4C40A2D701742DE041403B9994F129824C401768318F8BEE41408A0A316D2EDF4B401F6D5C4CDF0E424032402134D5854B406264439762A4424073E3BF7E573F4B40CA3E65381A4E4340391B287128384B4098C3C80CD2234440585FB4D32E374C4098C3C80CD2234440585FB4D32E374C40FE49A2AD88F143406775822944974C40C3691AE97DAB434010D59670EBAB4C40' as geometry) as geom

) as poly

WHERE ST_Intersects(point_database.point_geom, poly.geom ))

I can't test as I don't have your other tables, but let me know if that works...

  • Thanks that nailed it. As my friend would say, "you da real MVP dawg". – Echelon_One Sep 12 '16 at 18:56
  • NICE. Glad it worked!!! – DPSSpatial Sep 12 '16 at 18:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.