2

I am using PostgreSQL 9.3 and PostGIS 2.1.I imported a shapefile into my database. In that the_geom data type is "LINESTRING". Now I need a query to find the lines within a given radius for a particular latitude, longitude.

For example:

Consider I have latitude and longitude (12.4444 78.898900) for the given radius of 5000 meters I have to get the line details.

I tried a lot regarding this, I can't find a solution. Help me to solve this.

3

You'll find it easier to reason about your problem (coordinates in lon/lat, parameters in meters) if you work using the geography type rather than the geometry type. Geography type takes in lon/lat coordinates, but lets you ask questions and get answers in metric units.

ALTER TABLE mytable ALTER COLUMN the_geom TYPE geography USING geography(the_geom);
ALTER TABLE mytable RENAME COLUMN the_geom TO geog;
SELECT * FROM mytable WHERE ST_DWithin(geog, 'POINT(78.898900 12.4444)'::geography, 5000);

Note that the coordinate order for PostGIS is x/y, lon/lat. The x or longitude always comes first.

  • .i am trying to create index for geog column but it showing error "ERROR: index row requires 11488 bytes, maximum size is 8191" what i have to do for that.. – Subburaj May 13 '14 at 6:27
  • You're probably using the btree index binding, not the GIST. Correct syntax: CREATE INDEX myidx ON mytable USING GIST (mygeocolumn) – Paul Ramsey May 13 '14 at 16:41

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.