CODEWITHSUNDEEP
phppostgresqlpostgispgadmin
Tested
Tested

Reputation: 789

I have two tables, 1st country 2nd is state queried using ST_contains the state lies within the country or not

I am a new to postGIS and breaking my head across these queries ....... I need some help

I have two tables, 1st country(cid int,countryname text,coutrycoordinates geometry) and the other is state(sid int,statename text,statecoordinates geometry) wherein I have to write a query using ST_contains the state within the country but then my query are not working

Now suppose I have Inserted

INSERT into country  VALUES (
1,'country1',
'POLYGON ((1 5,4 5,4 7,1 7,1 5))');

INSERT into state  VALUES (
1,'state1',
'POLYGON ((2 5,3 5,3 6,2 6,2 5))');

works and gets inserted but then the value stored for geomertry coloumn is something of this sort 

01030000000100000005000000000000000000F03F000000000000F03F000000000000F03F0000000000001040000000000000104000000000000010400000000000001040000000000000F03F000000000000F03F000000000000F03F for country and 

010300000001000000050000000000000000000040000000000000004000000000000000400000000000000840000000000000084000000000000008400000000000000840000000000000004000000000000000400000000000000040  for state in the postreSQL db

I have a web page with 2 text fields and a drop down with ST_Contains and ST_Intersects and a submit button on click should display state lies in the country or not.

select c.cid  from country as c, state as s  where ST_Contains('POLYGON ((1 1,1 4,4 4,4 1,1 1))', 'POLYGON ((2 2,2 3, 3 3, 3 2, 2 2))')

the above works but selects all the rows a cross join in both table and not just that one c.cid.

select c.cid from country as c, state as s  where ST_Contains(
01030000000100000005000000000000000000F03F000000000000F03F000000000000F03F0000000000001040000000000000104000000000000010400000000000001040000000000000F03F000000000000F03F000000000000F03F, 010300000001000000050000000000000000000040000000000000004000000000000000400000000000000840000000000000084000000000000008400000000000000840000000000000004000000000000000400000000000000040)

If this is query then error returned

NOTICE:  identifier "f03f000000000000f03f000000000000f03f0000000000001040000000000000104000000000000010400000000000001040000000000000f03f000000000000f03f000000000000f03f" will be truncated to "f03f000000000000f03f000000000000f03f000000000000104000000000000"
ERROR:  syntax error at or near "F03F000000000000F03F000000000000F03F0000000000001040000000000000104000000000000010400000000000001040000000000000F03F000000000000F03F000000000000F03F"
LINE 2: 01030000000100000005000000000000000000F03F000000000000F03F00...
                                              ^

This the one which being executed in the php page wherein I have written this code

May I know where am I going wrong.

Upvotes: 0

Views: 160

Answers (1)

Peter Schoep
Peter Schoep

Reputation: 195

You are almost there, only missing quotes:

select c.cid from country as c, state as s  where ST_Contains(
'01030000000100000005000000000000000000F03F000000000000F03F000000000000F03F0000000000001040000000000000104000000000000010400000000000001040000000000000F03F000000000000F03F000000000000F03F', '010300000001000000050000000000000000000040000000000000004000000000000000400000000000000840000000000000084000000000000008400000000000000840000000000000004000000000000000400000000000000040')

The 'weird' geometry is because Postgres transforms the Polygon into a geometry. You can transform them to text again by using ST_AsText()

SELECT ST_AsText( state1 ) FROM state;

Upvotes: 1

Related Questions