0

I hope this is the correct forum but will ask anyway:

I have lots of Mesh data I want to store. Mesh is actually triangles. I hope it's not too of beginner question but wouldn't Geometry type of SQL Server be the best way to store that?

Specifically I'm interested in storing FBX file that are created in other programs sometimes in different formats such Autodesk Revit then converted to FBX. My other option is use file system which is risk and not managed. Or using binary field but storing large binary data in a database rarely ends well I understand.

This question is not about how to actually load the data I know this could be a problem but what SQL datatype should I use.

I was planning to make the FBX files very small so that for example I can have lookup tables of types. Example: a door will always be a single FBX and then the table will have: FBXType FBXData 1 (door) 2 (furniture)

At the moment is just querying so I can build AssetBundles for Unity. Then I was thinking if it is geometry maybe I can use the smart indexes and functions like intersect that is available for geometry type.

The data will be changing that is the reason I want it in a database however it will probably be in deliveries (this one day of the month everything is updated) and probably new rows (will have another column IsDeleted) can even be a new table but not a must. If splitting by type my colleague says only few FBX files will be large for buildings skeleton but even this is not sure because we can split by floor. Most FBX files will be 5MB or less

Improve this question
1
  • If you aren't actually going to do anything with the data other than store it, I would suggest using source control like Git to store and track changes to your FBX. I would also use the ASCII format when possible for FBX files so changes in source control would be discernable. FBX can be either binary or ASCII format. ASCII format uses a tree structured document with clearly named identifiers.
    – SqlZim
    Commented Jan 7, 2017 at 14:13

2 Answers 2

Reset to default
3

Are you considering using SQL Server to query data inside the mesh? For example, are you trying to pass in a list of points, and then have SQL Server figure out whether points in that area match up to any of the mesh data for existing rows inside the database?

If so, you're gonna have a bad time - that's not what SQL Server is designed for.

If, on the other hand, you're just using SQL Server as a key/value store ("give me the mesh data for this desk design") then you can use any data type you like, like JSON or XML or BINARY. You're correct, though, by saying that this rarely ends well. If you're looking for key/value storage, then use a key/value data store instead.

Improve this answer
3
  • (You can normally use any contemporary SQL database as a key/value store with reasonable --if not optimal-- efficiency; and it might make sense if you also use it to store many other things.)
    – joanolo
    Commented Jan 7, 2017 at 14:11
  • 2
    The problem is the transaction log. If your values are large and they change often, you're going to be pouring that data into the log file, bloating your log file backups, making DR replication more challenging, etc.
    – Brent Ozar
    Commented Jan 7, 2017 at 14:43
  • I see your point. Good. I guess that a number of those problems are going to happen on any data store that has replication (although they may make less use of log files because there are [normally] less ACID guarantees). AFAIK, a mesh, as used in CAD or Simulation programs, can be really large, but you do not normally change it that often... I guess a good compromise might be to store pieces of data, assuming you do not change everything every time.
    – joanolo
    Commented Jan 7, 2017 at 14:53
0

I think this has been implemented in the past, there is a whole chapter about Triangulation and Tesselation in Pro Spatial with SQL Server 2012. I think the method used is to store points and solve the mush in runtime however I don't have full access to the book. In the preview on Google Books I could see they are using c# WPF to reconstruct the mesh from points.

another useful example is here: https://alastaira.wordpress.com/2011/01/06/examining-3d-terrain-of-bing-maps-tiles-with-sql-server-2008-and-wpf-part-3/

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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