-1

What I'm trying to do is to find places which are inside or with 10 meters distance of a street.
My streets table has a geometry column which coordinates are saved from.
A sample coordinate looks like 

{ "type": "Feature", "properties": { "id": 4.000000, "osm_id": 69551269.000000, "type": "tertiary", "name": "Street name", "tunnel": 0, "bridge": 0, "oneway": 1, "ref": null, "z_order": 4.000000, "access": null, "service": null, "class": "highway" }, 
   "geometry": { "type": "LineString", 
       "coordinates": [ [ 45.055701068545773, 37.537045660463036 ], [ 45.055566036085651, 37.536995369044007 ], [ 45.054243455583901, 37.536797891405229 ], [ 45.053941120336447, 37.536756233346466 ], [ 45.053692177812167, 37.536712228354787 ], [ 45.052483758831642, 37.536435290273943 ], [ 45.052157870436275, 37.536344765719662 ], [ 45.051875819394468, 37.536229430731993 ], [ 45.05173206975504, 37.536151395213466 ], [ 45.051607347035826, 37.536067827638817 ], [ 45.051492766419436, 37.535979063284202 ], [ 45.050636052096081, 37.535314881276747 ], [ 45.050383253896371, 37.535111536305749 ], [ 45.050164989137727, 37.534882458892014 ], [ 45.050017048546714, 37.534692692604175 ], [ 45.049976061040212, 37.534639970433204 ], [ 45.049796436855189, 37.534394380670221 ], [ 45.049439032503869, 37.533859196152598 ], [ 45.049149186292141, 37.533424929749174 ], [ 45.048739143588875, 37.532811039160741 ], [ 45.048373357334377, 37.532213577102539 ], [ 45.048231284075598, 37.531903279047071 ], [ 45.048143022635173, 37.531710579093094 ], [ 45.047949568309946, 37.531336494754463 ], [ 45.047873628267183, 37.531189895267971 ], [ 45.047984772303266, 37.53111303321586 ] ] } },

My question is that should the coordinates be saved as polygon or LineString.
GeoJson authoritive indicates that LineString has no inside or outside but the data which I've got from openstreet(above is a sample) has a type of LineString.
So Which one should I use?

Edit:
A sample LineString from openstreetmap looks like this:
enter image description here enter image description here

I drew them by drawing a polygon with coordinates.

e4c5
  • 52,766
  • 11
  • 101
  • 134
Mehrdad Shokri
  • 1,974
  • 2
  • 29
  • 45

2 Answers2

0

Either of the two of geometry types, linestrings and polygon would work for you in your case. But i think it is quite straightforward to convert the linestrings to polygon and just use ST_Buffer the polygon with 20m...

Teng Ma
  • 353
  • 2
  • 9
  • As you see I have plotted two polygons with linestring coordinates. I can convert a linestring to polygon via ``st_buffer``, my question is that will those curved be represented as polygon area? or just the street itself will be converted to a polygon – Mehrdad Shokri Sep 22 '16 at 14:14
  • Like i said, either of the two geometry types would work for just plotting. Geojson is isolated with Openlayers right? And you are talking about get a 20m buffer and all the rest part of the area that inside the polygon for your mysql table right? – Teng Ma Sep 22 '16 at 14:19
  • I'm confusing about the question. Could you share your OL code? – Teng Ma Sep 22 '16 at 14:35
  • Here is what i would do in OL, but may not be exactly what you did. I convert the linearRing to polygon and get the feature from the output geometry and add it as a vector layer. – Teng Ma Sep 22 '16 at 14:38
  • Just one question: how can I create a buffer from lattitude longtitude points? It seems st_buffer(POINT(45.0557011 37.5370457),10) is not working – Mehrdad Shokri Sep 22 '16 at 14:39
  • Well, this one is much clear to me. So there are many ways to do that. Quick answer would be you can do it in mysql at your database level, or if you want to do it on the fly, use some library like turf.js. – Teng Ma Sep 22 '16 at 14:42
  • Can you share what is not working? I see POINT(45.0557011 37.5370457) that would be in lat long order in mysql, am i right? And can you check in your OL code, it is in long lat order. – Teng Ma Sep 22 '16 at 14:53
  • this is the complete code: ``SET @line = ST_GeomFromText('LINESTRING(45.0557011 37.5370457, SOME_OTHER_LAT_LNGS)');");`` ``SET @buffer=st_buffer(@line,10)`` ``SET @pt = ST_GeomFromText('POINT(45.024310 37.550785)');`` ST_BUFFER doesn't support lat lngs and that is the problem – Mehrdad Shokri Sep 22 '16 at 14:58
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/123963/discussion-between-teng-ma-and-mehrdad). – Teng Ma Sep 22 '16 at 15:01
0

SET @line = ST_GeomFromText('LINESTRING(44.9894318 37.496227, 44.9901579 37.4964403)',4326); SET @pt = ST_GeomFromText('POINT(45.00 37.4964)',4326); SET @buffer=st_buffer(@line,0.0124274); SELECT ST_WITHIN(@pt,@buffer)

20 meters = 0.0124274 mile, Difference between Geography coordinate system and geometry coordinate system: https://en.wikipedia.org/wiki/Geographic_coordinate_system

Teng Ma
  • 353
  • 2
  • 9
  • when I create a line with: ``SET @line = ST_GeomFromText('LINESTRING(45.0557011 37.5370457, OTHERPOINTS)',4326);`` running ``SET @buffer=st_buffer(@line,10)`` gives me error: ``Incorrect arguments to st_buffer`` – Mehrdad Shokri Sep 22 '16 at 17:01
  • oh what version of MySQL are you using? I remember there used to be a deprecated function in an older version of MySQL – Teng Ma Sep 22 '16 at 17:16
  • Mysql version 5.7 – Mehrdad Shokri Sep 22 '16 at 17:22
  • `set @point = ST_geomfromtext('Point(22.2 33.3)'); set @line = ST_geomfromtext('LineString(22 33, 22.5 33, 22.5 33.5, 22 33.5, 22 33)'); SELECT ST_WITHIN(@point, ST_BUFFER(@line, 10))` lets forget about the web mercator projection for now. Can you try to run this and let me know if it returns 1 or any error? – Teng Ma Sep 22 '16 at 17:40
  • yes it executes, but you know what, trying to run st_buffer with lat lng returns a polygon with lat lng on another place on earth. if you are curious where it is it is: ``"POLYGON((51.15086219116723 29.609308868298207,52.580370111259136 30.950745048572333,53.720709221131344 32.545289069240866,54.52805692830102 34.331663497820095,54.97138731314743 ))"`` (many points removed because of char limitation here) – Mehrdad Shokri Sep 22 '16 at 17:48
  • `SET @line = ST_GeomFromText('LINESTRING(44.9894318 37.496227, 44.9901579 37.4964403)',4326); SET @pt = ST_GeomFromText('POINT(45.00 37.4964)',4326); SET @buffer=st_buffer(@line,0.0124274); SELECT ST_WITHIN(@pt,@buffer)` And 0.0124274 mile is 20 meters, change the buffer distance value to 0.0000001 mile to see different result. – Teng Ma Sep 22 '16 at 17:56
  • First if we are talking about geography object like a point location in latitude and longtitude then we need to define a SRID in its definition. It will impact the buffer function and other spatial operation. Otherwise it is considered to be just a point in a default coordinate system where 20 meters means nothing there. Second is the default unit of buffer is in mile not in meter. – Teng Ma Sep 22 '16 at 18:07
  • in the comment you provided setting buffer distance to 0.0000001 moves the polygons points just on the map but setting it to 0.0124274 moves the points to far (more than 1km). What am I missing here?. (I didn't provide SRID to any shapes here) – Mehrdad Shokri Sep 22 '16 at 18:13
  • No i am saying in the example, i first run a buffer function of 20 meters, and you will see the point is located in the 20 meter buffer zone of the line. But when you decrease the buffer distance to really small like 0.000001 mile which is in sub meter, you will get result that point not in the buffer zone after you did replace the buffer distance in st_buffer function. The point position has not been changed. – Teng Ma Sep 22 '16 at 18:17
  • I now seem really dumb but bear with me. First of all when I set SRID in ST_GeomFromText,it gives me error in st_buffer line indicating ``Invalid GIS data provided to function st_geometryfromtext.`` mysql version is: 5.7.10. Second I ran your exact example, any got the polygon same for your example, the first point is located at ``44.98592911826011 37.50815056873545`` which is >1 of main road. – Mehrdad Shokri Sep 22 '16 at 18:26
  • I suspect your ST_geofromtext is having correct input. There is nothing wrong W/O srid. Try this : `SELECT ST_GeomFromText('POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239, -71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))',4326);` – Teng Ma Sep 22 '16 at 18:51
  • it returns ``+"ST_GeomFromText('POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239, -71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))',4326)": b"æ\x10\x00\x00\x01\x03\x00\x00\x00\x01\x00\x00\x00\x05\x00\x00\x00b…ÇÁ^ËQÀíˆü\rõ1E@(¤o$_ËQÀ\t\x07^¦÷1E@GÞÑæ]ËQÀx\x1CQ\x0Eø1E@Hq§ƒ]ËQÀëÚîuõ1E@b…ÇÁ^ËQÀíˆü\rõ1E@" `` I don't know what those jibberish are for – Mehrdad Shokri Sep 22 '16 at 18:55
  • Well, i never see that before. It is beyond my level. – Teng Ma Sep 22 '16 at 19:13
  • what about passing SRID? Do you know why it throws error when a SRID is set? – Mehrdad Shokri Sep 22 '16 at 19:34
  • Never had a problem with SRID , see official document: https://dev.mysql.com/doc/refman/5.7/en/gis-wkt-functions.html – Teng Ma Sep 22 '16 at 19:57
  • Yeah, already saw that. The docs say if srid types are not equal the type error is thrown. I suspect linetring with srid 4326 is not compatible with a number. Any thoughts on that? – Mehrdad Shokri Sep 22 '16 at 20:04