使用 PHP pack() 将 WKT 转换为 WKB
我正在使用准备好的语句将数据插入数据库，问题是我无法使用
INSERT INTO table (polygon) VALUES (GeomFromText(POLYGON((?,?,?,?,?,?))))
为什么？好吧，似乎 GeomFromText 本身被解释为文本：/ 所以我想我应该尝试将纯 WKB 字符串推入数据库，问题是我无法真正弄清楚如何将 WKT 打包到 WKB 中。
有人可以帮助我使用以下格式描述来完成此操作： http: //dev.mysql.com/doc/refman/5.0/en/gis-wkb-format.html pack() 的文档位于： http://php.net/manual/en/function.pack.php
I'm using prepared statements to insert data into my database, problem is I'm not able to use
INSERT INTO table (polygon) VALUES (GeomFromText(POLYGON((?,?,?,?,?,?))))
why? well, seems the GeomFromText itself is interpreted as text :/
so I figured I'd try pushing pure WKB strings into the db instead, problem is I can't really figure out how to pack a WKT into WKB.
Could someone help me do this with this format description: http://dev.mysql.com/doc/refman/5.0/en/gis-wkb-format.html
and the doc for pack() over at:
http://php.net/manual/en/function.pack.php
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这非常简单。获取规范并使用“C”代表字节，“V”代表字节uint32 和“d”表示
pack格式字符串中的双精度数。但是，我的建议是不要这样做。首先，重写内置函数是浪费时间。其次，使用 sql 传输二进制数据很容易出错（例如考虑编码问题）。第三，根据 http://dev.mysql。 com/doc/refman/5.0/en/creating-spatial-values.html 你甚至不需要GeomFromText，因为mysql已经将不带引号的wkt视为二进制。This is pretty straightforward. Grab a specification and use "C" for bytes, "V" for uint32's and "d" for doubles in your
packformat strings. However, my advice is not to do that. First, rewriting a build-in function is a waste of time. Second, transferring binary data with sql is error prone (think of encoding issues for example). And third, according to http://dev.mysql.com/doc/refman/5.0/en/creating-spatial-values.html you don't even needGeomFromText, because mysql already treats unquoted wkt as binary.我看到两个问题。首先，GeomFromText 函数接受一个字符串，因此它应该类似于
GeomFromText('POLYGON((0, 1, 2))')（注意引号）。其次，由于POLYGON...文本是字符串文字，因此应将其替换为通配符，而不是单个片段。您的查询应如下所示：然后您将在应用程序中而不是在语句中构建
POLYGON((?, ?, ?, ?, ?, ?))字符串。由于 PHP 具有安全的字符串处理，我建议使用sprintf('POLYGON((%d, %d, %d, %d, %d, %d))', $var1, $var2, $var3, $ var4, $var5, $var6)（sprintf 在 C 中是危险的）。或者，您可以使用 MySQL 的空间函数来生成点。我认为这就是您想要做的，但您没有通过 GeomFromText 传递它们。要使用 MySQL 的空间函数构建多边形，文档建议您需要执行以下操作：
在 MySQL 5.1.35 之前，您需要执行以下操作：
I see two problems. First, the GeomFromText function takes a string, so it should look like
GeomFromText('POLYGON((0, 1, 2))')(note the quotation marks). Second, since thePOLYGON...text is a string literal, it should be replaced with the wildcard, not the individual pieces. Your query should look like this:You would then build the
POLYGON((?, ?, ?, ?, ?, ?))string in the application, not in the statement. Since PHP has safe string handling I would recommend usingsprintf('POLYGON((%d, %d, %d, %d, %d, %d))', $var1, $var2, $var3, $var4, $var5, $var6)(sprintf is dangerous in C).Alternatively, you can use MySQL's spatial functions to generate the points. I think this is what you were trying to do, but you don't pass them through GeomFromText. To build a polygon with MySQL's spatial functions, the documentation suggests you would need to do:
Prior to MySQL 5.1.35 you would need to do: